Add Google sheet with data using Google v4 API

Question

I am using Python. I need to add a worksheet to spreadsheets using Google API v4. I can create a sheet using batchUpdate with a spreadsheet id and addSheet request (it returns shetId and creates an empty sheet). But how can I add data to it?

data = {'requests': [ { 'addSheet':{ 'properties':{'title': 'New sheet'} } } ]} res = service.spreadsheets().batchUpdate(spreadsheetId=s_id, body=data).execute() SHEET_ID = res['replies'][0]['addSheet']['properties']['sheetId'] 

Answer 1

You can add this code to write data to Google Sheet. In a document - Reading and writing cell values

Tables can have multiple sheets, with each sheet having any number of rows or columns. A cell is a location at the intersection of a particular row and column and may contain a data value. The Google Sheets API provides spreadsheets.values to make reading and writing values ​​simple.

Single band recording

To write data in one range, use spreadsheets.values.update request:

 values = [ [ # Cell values ... ], # Additional rows ... ] body = { 'values': values } result = service.spreadsheets().values().update( spreadsheetId=spreadsheet_id, range=range_name, valueInputOption=value_input_option, body=body).execute() 

The body of the update request should be ValueRange , although the only required field is values . If range specified, it must match the range in the URL. In ValueRange you can specify majorDimension . The default is ROWS. If COLUMNS is specified, each inner array is written to a column instead of a row.

Record multiple ranges

If you want to write some discontinuous ranges, you can use spreadsheets.values.batchUpdate request:

 values = [ [ # Cell values ], # Additional rows ] data = [ { 'range': range_name, 'values': values }, # Additional ranges to update ... ] body = { 'valueInputOption': value_input_option, 'data': data } result = service.spreadsheets().values().batchUpdate( spreadsheetId=spreadsheet_id, body=body).execute() 

The batchUpdate request body must be a BatchUpdateValuesRequest object that contains a ValueInputOption and a ValueRange list (one for each recorded range). Each ValueRange object specifies its own range , majorDimension and input data.

Hope this helps.

Answer 2

It turned out that after creating the sheet inside the spreadsheet, you can access the range of 'nameofsheet! A1 'ie

 service.spreadsheets().values().update(spreadsheetId=s_id, range='New sheet!A1', body=data_i, valueInputOption='RAW').execute() 

This request will send data to a newly created sheet named "New Sheet"

Answer 3

You can also try the “more intuitive” library that I wrote for google api v4 sheets, pygsheets , since I found the default client to be cumbersome.

 import pygsheets gc = pygsheets.authorize() # Open spreadsheet sh = gc.open('my new ssheet') # create a worksheet sh.add_worksheet("new sheet",rows=50,cols=60) # update data with 2d vector sh.update_cells('A1:B10', values) # or skip let it infer range from size of values sh.update_cells('A1', values)