CODEWITHSUNDEEP
pythongoogle-sheetsgoogle-sheets-api
Alexandr
Alexandr

Reputation: 395

Add Google sheet with data using Google API v4

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']

Upvotes: 9

Views: 5763

Answers (3)

Nithin
Nithin

Reputation: 5840

You could also try a 'more intuitive' library i wrote for google sheets api v4 , pygsheets as i found the default client bit 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)

Upvotes: 3

Mr.Rebot
Mr.Rebot

Reputation: 6791

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.

Upvotes: 4

Alexandr
Alexandr

Reputation: 395

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'

Upvotes: 4

Related Questions