Search code examples
python-3.xgoogle-sheetsgoogle-apigoogle-sheets-apigspread

Updating Cols in google Sheets

I am trying to copy columns from one sheet to another sheet. I get the columns in response from source sheet. I need to insert them into the sheet. Since methods like insertDimension and insertRange cannot do it. I used request = service.spreadsheets().values().update(spreadsheetId=to_spreadsheet_id, range=range_, valueInputOption = "USER_ENTERED", body={"values": response}) but it gives me error like this :- googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/1sERXk6YshuNOKi4ggp11a36uf5SGutLg7DAP5vitOoQ/values/Working%20Analysis%21G2?valueInputOption=USER_ENTERED&alt=json returned "Invalid values[1][0]: struct_value { fields { key: "effectiveFormat" value { struct_value { fields { key: "backgroundColor" value { struct_value { fields { key: "blue" value { number_value: 1.0 } } fields { key: "green" value { number_value: 1.0 } Its a very long text......

AND if I use other way which is commented in the code block I get the following error Details: "Invalid requests[0].updateCells: Attempting to write row: 15000, beyond the last requested row of: 14999">

def copy_column(service, from_spreadsheet_id, to_spreadsheet_id, from_sheet='Analysis', to_sheet_id, from_column='F', from_column_till='K', to_column='G'):

    request = service.spreadsheets().get(spreadsheetId=from_spreadsheet_id, ranges=[
        from_sheet + "!" + from_column + ":" + from_column_till], includeGridData=True)

    response = request.execute()["sheets"][0]["data"][0]["rowData"]

    range_ = "Working Analysis!G2"

    print(response)

    # value_range_body = {
    #     "requests": {
    #         "insertDimension": {
    #             "range": {
    #                 "sheetId": to_sheet_id,
    #                 "dimension": "COLUMNS",
    #                 "startIndex": 6,
    #                 "endIndex": 11
    #             },
    #             "inheritFromBefore": True
    #         }
    #     }
    # }
    # request_1 = service.spreadsheets().batchUpdate(spreadsheetId=to_spreadsheet_id, body=value_range_body)
    # response_1 = request_1.execute()

    # body = {
    #     "requests": {
    #         "updateCells": {
    #             "rows": response,
    #             "fields": "userEnteredFormat, userEnteredValue",
    #             # "start":{
    #             #     "sheetId": to_sheet_id,
    #             #     "rowIndex": 1,
    #             #     "columnIndex": 6
    #             # },
    #             "range": {
    #                 "sheetId": to_sheet_id,
    #                 "startRowIndex": 1,
    #                 "startColumnIndex": 6,
    #                 "endColumnIndex": 13
    #             },

    #         }
    #     }
    # }

    request = service.spreadsheets().values().update(spreadsheetId=to_spreadsheet_id,
                                                   range=range_, valueInputOption = "USER_ENTERED", body={"values": response})

    response = request.execute()

    return print('Done')
Solution

  • I think that the response values from service.spreadsheets().get() cannot be directly used to service.spreadsheets().values().update(). From your commented script, I guessed that you might want to copy not only the values but also the cell format.

    In this case, how about the following modification?

    Modified script:

    sheetId = "###"  # Please set the sheet ID of the sheet "Working Analysis"

request = service.spreadsheets().get(spreadsheetId=from_spreadsheet_id, ranges=[from_sheet + "!" + from_column + ":" + from_column_till], includeGridData=True)
response = request.execute()["sheets"][0]["data"][0]["rowData"]

requests = {
    "requests": [
        {
            "updateCells": {
                "start": {"sheetId": sheetId, "rowIndex": 1, "columnIndex": 6},
                "rows": response,
                "fields": "*",
            }
        }
    ]
}
request = service.spreadsheets().batchUpdate(spreadsheetId=to_spreadsheet_id, body=requests)
response = request.execute()
print("Done")

    For example, if you want to copy only the values, you can use service.spreadsheets().values().update() as follows.

    request = service.spreadsheets().values().get(spreadsheetId=from_spreadsheet_id, range=from_sheet + "!" + from_column + ":" + from_column_till)
response = request.execute()["values"]
range_ = "Working Analysis!G2"
request = service.spreadsheets().values().update(spreadsheetId=to_spreadsheet_id, range=range_, valueInputOption="USER_ENTERED", body={"values": response})
response = request.execute()
return print("Done")

    References: