Write to 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

In the Google Sheets API, spreadsheets.values.append searches the input range for a table of existing data and appends values to the next row of that table, starting with its first column.^[1] By default, values appended through the Google Sheets API overwrite any data after the table, and insertDataOption=INSERT_ROWS writes them into new rows instead.^[1] RapidMiner's Write Google Sheets operator, by default, writes from the first cell of the sheet and blanks the cells after it, which overwrites the entire sheet.^[3] When RapidMiner's Write Google Sheets operator appends, Google Sheets finds the last non-empty row and inserts the new values below it, and the new data need not match the format of the data already there.^[3] The gsheets community extension for DuckDB overwrites the entire sheet by default, and appends below existing data when its overwrite_sheet and overwrite_range options are both false.^[4] When the gsheets extension appends below existing data, it writes no header by default.^[4] Decide whether a repeated write should replace the sheet or add to it, and set that choice explicitly rather than leave it to a tool's default. (Meridian's view)

Google Sheets API value writes take a required valueInputOption parameter that controls how the input data is interpreted.^[1] With valueInputOption set to RAW, the Google Sheets API inserts the input as a string without parsing it, so "=1+2" stays the string rather than becoming a formula.^[1] With valueInputOption set to USER_ENTERED, input is parsed as if entered into the Sheets user interface, so "Mar 1 2016" becomes a date and "=1+2" a formula.^[1] Under USER_ENTERED, formats can also be inferred, so "$100.15" becomes a number with currency formatting.^[1] The Google Sheets API always handles non-string values such as booleans and numbers as RAW.^[1] Use RAW for codes and text that must read exactly as written, and USER_ENTERED only where Sheets is meant to read dates and amounts. (Meridian's view) By default, RapidMiner's Write Google Sheets operator converts date and time columns to their corresponding Google Sheets types using the spreadsheet's time zone.^[3] With Alteryx Designer's Google Sheets Output tool, Google Sheets might replace specific characters, so that a tab becomes a space.^[5]

The Google Sheets API allows 300 write requests per minute per project and 60 per minute per user per project.^[6] A request beyond the Google Sheets API's per-minute quota receives a 429: Too many requests response, and the quotas refill every minute.^[6] Each Google Sheets API batch request, including any subrequest, counts as one API request toward the usage limit.^[6] Send a table to an online spreadsheet in a few large batches rather than one request per row. (Meridian's view) The Google Sheets API sets no hard size limit on a request, though a payload of at most 2 MB is recommended to speed requests up.^[6] When Google Sheets processes one API request for more than 180 seconds, the request returns a timeout error.^[6] Apache Hop's Google Sheets output retries Google Sheets API errors with status codes 429, 500 or 503, three times by default, using exponential backoff after a first delay of 2 seconds.^[7]

Apache Hop's Google Sheets output needs a Google service account and a Google Cloud project with the Google Drive and Google Sheets API enabled.^[7] Apache Hop's Google Sheets output can delete and recreate a sheet that exists, which can avoid excessive versioning information in pipelines that write to a Google Sheet frequently.^[7]

The method

The Google Sheets API writes a single range with spreadsheets.values.update, multiple ranges with spreadsheets.values.batchUpdate, and appended values with spreadsheets.values.append.^[1] In the Google Sheets API, combining multiple updates into one batchUpdate call improves efficiency.^[1] Inserting rows, or updating the formatting and other properties of a sheet, needs the batchUpdate method of the spreadsheets resource rather than spreadsheets.values.^[1] All Google Sheets API requests are applied atomically, so if any request is not valid, the entire update is unsuccessful and none of its changes are applied.^[6]

Google Sheets originated from XL2Web, a web-based spreadsheet application developed by 2Web Technologies, and Google acquired XL2Web in 2006.^[2] XL2Web became Google Labs Spreadsheets, which launched as a test for a limited number of users on June 6, 2006.^[2]

Compared with the alternatives

Google Sheets lets users create and edit files online while collaborating with other users in real time, and its revision history records which user made each change.^[2] A Microsoft Excel worksheet holds at most 1,048,576 rows by 16,384 columns, while a spreadsheet created in or converted to Google Sheets holds up to 20 million cells or 100MB.^[8][9] When a document is converted from Excel to Google Sheets, any cell with more than 50,000 characters is removed.^[9] Write to an online spreadsheet when people will open, share and keep editing the table in place, and to a workbook file when the table is a copy to send or keep. (Meridian's view)

Sources

1. [1]Read and write cell values | Google Sheets | Google for Developersdocumentation
2. [2]Google Sheets - Wikipediasecondary source
3. [3]Write Google Sheets - Rapidminer Documentationdocumentation
4. [4]DuckDB GSheetsdocumentation
5. [5]Google Sheets Output Tool (Deprecated)documentation
6. [6]Usage limits | Google Sheets | Google for Developersdocumentation
7. [7]Google Sheets output | Apache Hopdocumentation
8. [8]Excel specifications and limits | Microsoft Supportdocumentation
9. [9]Files you can store in Google Drive - Google Drive Helpdocumentation