Python returns #DIV/0! from Google Sheets instead of formula result

Question

I am working on a python script to retrieve and work with data from Google Sheets. So far, I can log in with no issues and read/write cell data.

However, as far as some cells containing formulas are concerned, the value python returns is #DIV/0!, instead of the result I see in the cell of the spreadsheet itself.

Why is that? I tried to specify the "Value Render Option" parameter as either FORMATTED_VALUE, UNFORMATTED_VALUE or FORMULA, but nothing works.

For example, one cell contains an IF formula, which shows up as "Positive" or "Negative" in the sheet itself. I want python to retrieve this value. Instead, it gets me #DIV/0! when I print out the values. Same goes for a calculation I do in the sheet itself (which generates a proper result when I view the sheet online).

My script resembles the example ones on the Google tutorial pages.

Thanks for helping out a begginer! Cheers!

Here's the script:

from __future__ import print_function
from pprint import pprint
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from googleapiclient import discovery
import pickle

SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']

spreadsheet_id = '1-V95KxzJCPglRa90KnvnHBvY2doffeAxuRrEBEA0mHg'

range_ = 'Finance_3!A1:D30'
credentials = None

if os.path.exists('token.pickle'):
    with open('token.pickle', 'rb') as token:
        credentials = pickle.load(token)

service = discovery.build('sheets', 'v4', credentials=credentials)

value_render_option = 'FORMATTED_VALUE' 

date_time_render_option = 'FORMATTED_STRING' 

request = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=range_, valueRenderOption=value_render_option, dateTimeRenderOption=date_time_render_option)
response = request.execute()

pprint(response)

How do you expect anyone to help you if you don't include the script? — Isma
What sort of data you want to read? Usually, such behavior occurs when you want to read formulas such as stocks or any serious formulas related to Google DB. — Dmytro Chasovskyi
I am indeed working with stock price data. However, the actual price (=GOOGLEFINANCE(stock,”price”)) is fetched correctly. But a formula based on said price does not work. For example a calculation of average prices doesn’t work. Or an IF formula (if price is > 100, for example, return “True” or “False”) doesn’t work, even though it is displayed correctly on the sheet. — Andrei Sota

Andrei Sota Andrei Sota · Accepted Answer · 2019-02-24T13:14:24

Seems like I found out the cause. There's nothing wrong with the code, I tested it on different spreadsheets.

When trying to fetch or otherwise work with GOOGLEFINANCE historical price data, the supplied value is NONE (or NULL). That's what's causing the DIV/0 issue. Even if your formula is based on a formula that's based on historical price, it still doesn't work.

The "realtime" price gets returned just fine though.. It's only the historical price that's not supported.

For price history I am now using Alpha Vantage. Works fine with python.

Cheers!

Python returns #DIV/0! from Google Sheets instead of formula result

1 Answers