Can't insert range to google sheets in Python

Question

I have Array with data table_data = [["11"],["22"]],[["33"],["44"]] And I want to insert this data into Google Sheets using sheet.values().update, but with GridRange, without Named Range I have the resolve for this:

service = build('sheets', 'v4', credentials=creds())
    sheet = service.spreadsheets()
    body = {'values': values}
    SAMPLE_RANGE_NAME_2 = 'costs!A1:B2'
    value_input_option = 'RAW'
    result = sheet.values().update(
        spreadsheetId=SAMPLE_SPREADSHEET_ID, range=SAMPLE_RANGE_NAME_2, valueInputOption=value_input_option, body=body).execute()

But it's unconvinient use with range A1:B2. I want to use something like this

'startColumnIndex': 0,
'endColumnIndex': 1,
'startRowIndex': 0,
'endRowIndex': 1

I try use batchUpdate for this, but it can accept only string parameter

spreadsheet_id = SPREADSHEET_ID  # TODO: Update placeholder value.

batch_update_spreadsheet_request_body = {
        "requests": [
            {
                "pasteData": {
                    "data": str(table_data),
                    "type": "PASTE_NORMAL",
                    "delimiter": ",",
                    "coordinate": {
                        "sheetId": 0,
                        "rowIndex": 0,
                        "columnIndex": 0
                    }
                }
            }
        ]
    }
request = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=batch_update_spreadsheet_request_body)
response = request.execute()

Answer 1

• You want to put values using GridRange.
• You want to use table_data = [["11"],["22"]],[["33"],["44"]] as the value.
  • I think that if you want to put the values with 2 rows and 2 columns, it becomes table_data = [["11", "22"], ["33", "44"]]. So I used this.
  • If you want to put 11, 22, 33 and 44 to A1, B1, A2 and B2, respectively, it becomes table_data = [["11", "22"], ["33", "44"]].
  • If you want to put 11, 22, 33 and 44 to A1, A2, B1 and B2, respectively, it becomes table_data = [["11", "33"], ["22", "44"]].

If my understanding is correct, how about using the method of "updateCells" of batchUpdate instead of "pasteData"? I think that there are several solutions for your situation. So please think of this as just one of them.

Modified script:

spreadsheet_id = SPREADSHEET_ID
sheetId = 0
table_data = [["11", "33"], ["22", "44"]]  # or table_data = [["11", "22"], ["33", "44"]]
rows = [{'values': [{'userEnteredValue': {'stringValue': f}} for f in e]} for e in table_data]
rng = {'sheetId': sheetId, 'startRowIndex': 0, 'startColumnIndex': 0}
fields = 'userEnteredValue'
body = {'requests': [{'updateCells': {'rows': rows, 'range': rng, 'fields': fields}}]}
request = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body)
response = request.execute()

• When the values are put to A1:B2, you can set the coordinate of the top left cell. So in this case, the GridRange becomes {'sheetId': sheetId, 'startRowIndex': 0, 'startColumnIndex': 0}

Note:

• If you want to use table_data = [["11"], ["22"]], [["33"], ["44"]], please modify rows to rows = [{'values': [{'userEnteredValue': {'stringValue': f[0]}} for f in e]} for e in table_data].

Reference:

• UpdateCellsRequest

If I misunderstood your question, I apologize.