JavaScript library for queueing data to be bulk written to the Google Sheets API on timed intervals to avoid violating the rate limit
npm install google-sheets-bulk-writer --save
- Google API v4 Authentication
const BulkWriter = require('google-sheets-bulk-writer');
const { google } = require('googleapis');
const auth = new google.auth.GoogleAuth({
keyFile: process.env.GOOGLE_CONFIG,
scopes: ['https://www.googleapis.com/auth/spreadsheets'],
});
const bulkWriter = new BulkWriter({
auth,
spreadsheetId: process.env.SPREADSHEET_ID,
});
Option | Type | Required | Default | Description |
---|---|---|---|---|
auth | google.auth.GoogleAuth | Yes | Required to use the Google Sheets API documentation. | |
spreadsheetId | String | Yes | Google Sheets identifier | |
interval | Number | No | 30000ms | The interval at which to append to the spreadsheet |
valueInputOption | String | No | USER_ENTERED | See Google Sheets API documentation |
insertDataOption | String | No | INSERT_ROWS | See Google Sheets API documentation |
errorHandler | Function | No | console.error | Handler to catch errors returned by the Google Sheets API |
bulkWriter.setErrorHandler((err) => {
console.log(`Failed to write values to spreadsheet`);
console.log(JSON.stringify(bulkWriter.getErrorValues(), null, 4));
bulkWriter.clearErrorValues();
});
bulkWriter.start();
bulkWriter.append(`A:H`, ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h']);
bulkWriter.append(`A:H`, ['b', 'b', 'c', 'd', 'e', 'f', 'g', 'h']);
bulkWriter.append(`Sheet2!A:H`, ['c', 'b', 'c', 'd', 'e', 'f', 'g', 'h']);
setTimeout(() => {
bulkWriter.append(`A:H`, ['d', 'b', 'c', 'd', 'e', 'f', 'g', 'h']);
bulkWriter.append(`Sheet2!A:H`, ['e', 'b', 'c', 'd', 'e', 'f', 'g', 'h']);
}, 25000);
Required to start the recurring bulk write.
If the bulk write needs to be stopped for whatever reason.
Calls stop()
and then start()
. This needs to be called if a new interval
is set.
Adds a row to be appended to the spreadsheet.
range
is expected to be a string. The sheetId is specified in the range. See Google Sheets API documentation for more information.
values
is a two-dimensional array of values to be appended to the spreadsheet.
This gets automatically called every interval
amount of time, but it is available to be used manually as well.
This is a wrapper function for google.sheets.spreadsheets.values.append()
. It gets automatically called by the flush()
functin.
Retrieve the interval
option value.
Set a new interval
value. Expects an integer as the parameter.
Retrieve the values set to be posted to a spreadsheet. cache
is an object with key values of different ranges. Allows for posting to multiple tabs.
Clears a single range field in the cache
object. Expects a string as the parameter.
Resets the cache
object.
Allows for custom handling of Google Sheets API errors. Expects a function as the parameter.