Googlesheet APIv4 getting empty cells

The way I solved this issue was converting the values into a Pandas dataframe. I fetched the particular columns that I wanted in my Google Sheets, then converted those values into a Pandas dataframe. Once I converted my dataset into a Pandas dataframe, I did some data formatting, then converted the dataframe back into a list. By converting the list to a Pandas dataframe, each column is preserved. Pandas already creates null values for empty trailing rows and columns. However, I needed to also convert the non trailing rows with null values to keep consistency.

# Authenticate and create the service for the Google Sheets API
credentials = ServiceAccountCredentials.from_json_keyfile_name(KEY_FILE_LOCATION, SCOPES)
http = credentials.authorize(Http())
discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?version=v4')
service = discovery.build('sheets', 'v4',
    http=http,discoveryServiceUrl=discoveryUrl)

spreadsheetId = 'id of your sheet'
rangeName = 'range of your dataset'
result = service.spreadsheets().values().get(
    spreadsheetId=spreadsheetId, range=rangeName).execute()
values = result.get('values', [])

#convert values into dataframe
df = pd.DataFrame(values)

#replace all non trailing blank values created by Google Sheets API
#with null values
df_replace = df.replace([''], [None])

#convert back to list to insert into Redshift
processed_dataset = df_replace.values.tolist()

I've dabbled in Sheetsv4 and this is indeed the behavior when you're reading a range of cells with empty data. It seems this is the way it has been designed. As stated in the Reading data docs:

Empty trailing rows and columns are omitted.

So if you can find a way to write a character that represents 'empty values', like zero, then that will be one way to do it.

I experienced the same issue using V4 of the sheets api but was able to workaround this using an extra column at the end of my range and the valueRenderOption argument for the values.get API

Given three columns, A, B and C any of which might contain a null value, add an additional column, D and add an arbitrary value here such as 'blank'.

Ensure you capture the new column in your range and add the additional parameter,

valueRenderOption: 'FORMATTED_VALUE'.

You should end up with a call similar to this:

sheets.spreadsheets.values.get({
  spreadsheetId: SOME_SHEET_ID,
  range: "AUTOMATION!A:D",
  valueRenderOption: 'FORMATTED_VALUE'
}, (err, res) => {})

This should then give you a consistent length array for each value, returning a blank string "" in the place of the empty cell value.