Add Google sheet with data using Google API v4

Question

I am using Python. I need to add sheet in spreadsheets using Google API v4. I can create a sheet using batchUpdate with spreadsheet id and addSheet request (it returns shetId and creates empty sheet). But how can I add data in 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 in Google Sheet. In the document - Reading & Writing Cell Values

Spreadsheets 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 the spreadsheets.values collection to enable the simple reading and writing of values.

Writing to a single range

To write data to a single range, use a 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 must be a ValueRange object, though the only required field is values. If range is specified, it must match the range in the URL. In the ValueRange, you can optionally specify its majorDimension. By default, ROWS is used. If COLUMNS is specified, each inner array is written to a column instead of a row.

Writing multiple ranges

If you want to write multiple discontinuous ranges, you can use a 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 body of the batchUpdate request must be a BatchUpdateValuesRequest object, which contains a ValueInputOption and a list of ValueRange objects (one for each written range). Each ValueRange object specifies its own range, majorDimension, and the data to input.

Hope this helps.

Answer 2

Figured out that after creating sheet inside spreadsheet you can access range 'nameofsheet!A1' i.e.

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

This request will post the data into new created sheet with name 'New sheet'