Read an online spreadsheet

Status

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Background

When to use it

A call to the Google Sheets API's spreadsheets.values.get requires an authorisation scope such as https://www.googleapis.com/auth/spreadsheets.readonly.^[2] Apache Hop's Google Sheets input transform requires a Google service account with its credential key file, and a Google Cloud project with the Google Drive and Google Sheets APIs enabled.^[4] OpenRefine can import any Google Sheet that has link-sharing turned on, given its web address (URL).^[5] OpenRefine's import of a Google Sheet by URL works only with Sheets files, not with .xls or other files hosted in Google Drive.^[5] Settle which account the reader signs in as before building on an online sheet. (Meridian's view)

The Google Sheets API returns values as FORMATTED_VALUE by default, calculated and formatted according to each cell's formatting.^[2][6] Google Sheets API formatting follows the spreadsheet's locale, not the requesting user's locale.^[6] With the Google Sheets API's UNFORMATTED_VALUE option, a cell formatted as currency holding 1.23 returns the number 1.23 rather than "$1.23".^[6] Ask for unformatted values when the numbers are going into a table. (Meridian's view)

The Google Sheets API omits empty trailing rows and columns from the values it returns.^[7] Pad each returned row to the width of the header rather than trusting the row lengths the reply gives. (Meridian's view) In the Google Sheets API, a range given without a sheet name, such as A1:B2, refers to the first visible sheet.^[1] Name the sheet in every range, so the read does not depend on the order of the tabs. (Meridian's view)

The Google Sheets API has per-minute quotas, including a read request limit of 300 per minute per project.^[8] A Google Sheets API request over the quota gets a 429: Too many requests response.^[8] A scheduled job that reads many ranges should retry with backoff rather than fail on the first refusal. (Meridian's view)

The method

A Google Sheets spreadsheet has a unique spreadsheet ID, which can be derived from the spreadsheet's URL and stays stable even if the spreadsheet's name changes.^[1] Each sheet within a Google Sheets spreadsheet has a unique numeric sheet ID, which also stays stable if the sheet's name changes.^[1] For Sheet1 holding A1=1, B1=2, A2=3 and B2=4, the Google Sheets API returns [[1,2],[3,4]] with majorDimension set to ROWS and [[1,3],[2,4]] with it set to COLUMNS.^[2]

Google Sheets stores a date as a serial number whose whole part counts the days since December 30, 1899 and whose fractional part counts the time as a fraction of a day.^[9] Google Sheets treats the year 1900 as a common year, not a leap year.^[9] Google Sheets date and time serial numbers store no time zone offset and are based on the spreadsheet's time zone.^[9] In the Google Sheets API, the dateTimeRenderOption parameter is ignored for FORMATTED_VALUE and otherwise defaults to SERIAL_NUMBER.^[2]

Google Sheets originated from XL2Web, a web-based spreadsheet application that Google acquired in 2006 and turned into Google Labs Spreadsheets.^[3] Google Sheets caps a document's size at 10 million cells.^[3]

Compared with the alternatives

Excel's default 1900 date system counts serial numbers from January 1, 1900 and treats 1900 as a leap year, while Google Sheets counts from December 30, 1899 and treats 1900 as a common year.^[10][11][9] Convert serial dates from each source with that source's own rule, rather than one rule for both. (Meridian's view)

Sources

1. [1]Google Sheets API Overview | Google for Developersdocumentation
2. [2]Method: spreadsheets.values.get | Google Sheets | Google for Developersdocumentation
3. [3]Google Sheets - Wikipediasecondary source
4. [4]Google Sheets input | Apache Hopdocumentation
5. [5]Starting a project | OpenRefinedocumentation
6. [6]ValueRenderOption | Google Sheets | Google for Developersdocumentation
7. [7]Read and write cell values | Google Sheets | Google for Developersdocumentation
8. [8]Usage limits | Google Sheets | Google for Developersdocumentation
9. [9]Date and number formats | Google Sheets | Google for Developersdocumentation
10. [10]Date systems in Excel | Microsoft Supportdocumentation
11. [11]Excel incorrectly assumes that the year 1900 is a leap year - Microsoft 365 Apps | Microsoft Learndocumentation