How to add new sheet to existing google sheet file

Anvil Q&A
1

What I’m trying to do:
I have existing google sheet file and I would like to add a new sheet to it
What I’ve tried and what’s not working:
searching the documentation and the forum

2

I don’t think Anvil provides an API to do that with code.

You may be able to do it with Google’s API, though (but I haven’t even followed this link, so take with a grain of salt): Sheets API  |  Google Developers

1 Like
3

I was hoping that anvil could do that :slightly_frowning_face:

1 Like
4

I am assuming @advancedhealthsys has moved on. … but today I encountered the same request on my current project.

The example Google Sheet’s API out there, all seem to handle authentication for you, and I am using some of the Google integration provided by Anvil. Which is to say I was scared off of trying to figure out how to blend authorization and authentication across multiple providers.

So I cobbled my solution together with vanilla REST and Anvil integration calls.

Here is what I did to add a new “tab” to an existing spreadsheet.

In a Server Module; using the requests library.

assuming you know the spreadsheet_id of the worksheet where you want to create the new “tab”.

 access_token = anvil.google.auth.get_user_access_token()

 body = {
    "requests": [
      {
        "addSheet": {
          "properties": {
            "title": sheet_title,
            "sheetType": "GRID",            
            "gridProperties": {
              "rowCount": 40,
              "columnCount": 10
            }
          }
        }
      }
    ]
  }
  gapi_url = f"https://sheets.googleapis.com/v4/spreadsheets/{spreadsheet_id}:batchUpdate"
  print(gapi_url)
  upd_result = requests.post(
    url=gapi_url,
    json=body,
    headers={
    'Authorization':
      'Bearer ' + access_token
  })

FWIW … Here is how I then subsequently added data to the new tab.

  # cg_prices is 2d array of Unix Timestamps and asset price at that time

  my_range = f"{sheet_title!r}!A1:B33"  
  p_list = [['TS', 'PRICE']]
  p_list.extend(cg_result['prices'])

  # so the p_list is like [['TS', 'PRICE'], [1663113600000, 1573.2052136951713], [1663200000000, 1635.3682061921256], [1663286400000, 1471.999592516298], ...

  # Needs to have same dimensions as the range.

  gapi_url = f"https://sheets.googleapis.com/v4/spreadsheets/{sheet_id}/values:batchUpdate"
  g_body =  {
                "valueInputOption":"RAW",
                "includeValuesInResponse": False,
                "responseValueRenderOption": "UNFORMATTED_VALUE",
                "responseDateTimeRenderOption": "FORMATTED_STRING",
                "data": [{"range": my_range, "values": p_list}],
            }
  
  upd_result = requests.post(
    url=gapi_url,
    json=g_body,
    headers={
    'Authorization':
      'Bearer ' + access_token
  })

If you dig through the documents referenced by @hugetim you can find other options for the parameters related to the values updates.

2 Likes