Implement GOOGLEFINANCE( ) in Google Sheets using python (and read back data)-CodePudding

On a regular Google Sheet (without using python), I have been able to use the GOOGLEFINANCE() formula and import stock market data (eg, to get the price at Open for NVDA stock: GOOGLEFINANCE("NVDA", "open", <start date>, <end date>, "DAILY") ).

With python 3 and using the google_auth_oauthlib and googleapiclient packages, I have been able to create Google Sheet (using python code) and do basic write/read values to any cell. I accomplished this by going through random videos on Youtube.

Question:

What is the syntax in python to implement a function (such as GOOGLEFINANCE() ) into a Google Sheet? Can someone share a sample python code, or documentation.

Basically, using python, I want to import historical data (for a stock) using the GOOGLEFINANCE() function available in Google Sheets.

CodePudding user response：

You can find the official documentation here also with samples on how to write data to cells.

The part relevant to what you're looking for is in the "writing values" sample when running values().update():

result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption=value_input_option, body=body).execute()
print('{0} cells updated.'.format(result.get('updatedCells')))

As you can see in the sample and the Google API Python docs, the update method takes a parameter called valueInputOption. You can set this to USER_ENTERED, so the values will be interpreted as if you entered them manually on the sheet.

This way you can just enter the formula as a string, as if you were typing it on the sheet ("=GOOGLEFINANCE("NVDA", "open", <start date>, <end date>, "DAILY")"), and the cell will run it as expected.