I have a sheet with 18 columns and 137 rows. Some of the columns are empty. When I make the get sheets call (I'm using the C# SDK) the data returned has the following structure which seems anomalous to me ...
Columns --- 19 (the RowID is tacked on at the end of the list)
Rows --- 137 (good) but when I examine the number of cells in the rows, I get varying numbers: 18, 18, 17, 16, 18. It appears to drop the null-valued cells. Is this intentional to drop null cells? If so, why doesn't it always drop null cells? It's a little strange that the cell count doesn't reflect the column count.
The only way I can figure to work around this is to correlate using the ColumnId since there is no guarantee that there will be exactly 18 cells returned for each row.
It appears to drop the null-valued cells. Is this intentional to drop null cells?
That is correct cells that have never been touched by the user are not sent via the API. This approach will reduce the payload and is beneficial for clients such as mobile.
If so, why doesn't it always drop null cells?
It will always drop the null cells (cells that have not been touched by the user).
However, there is a case where empty cell data is returned. This will occur when a user adds text in a cell, saves the sheet and then deletes the text. It will now appear that the cell is empty but we still store history for this cell so it is not truly a null cell or untouched cell.
The only way I can figure to work around this is to correlate using the ColumnId since there is no guarantee that there will be exactly 18 cells returned for each row.
If you need to have the cell match up to an exact column (or column position) then we do recommend using the column id as the key.