Simple Sheets

Reads and writes Google Sheets row data, perfect for sheets populated by Google Forms. This is a wrapper for the very-powerful-yet-overwhelming official Sheets API.

Authenticates via a Google Service Account by passing in the client_email and private_key values provided by the .json file that Google Service Accounts generate. See service-account-credentials.json for an example.

Now includes tools for clearing and seeding sheets with data, which is useful for writing automated tests against spreadsheets.

API

getRows

getRows(rows, options).then();

It returns a promise that resolves to an object that has arrays with the requested mappings.

rows is an array of objects that have the following properties:

[{
    label: "people", // This will be the label of the array
    range: "A2:B", // In A1 format
    mapping: ["firstName", "lastName"] // These are what the columns will be labeled
},{
    label: "locations",
    range: "'Cities'!C2:C",
    mapping: ["city"]
}]

options include:

• spreadsheetId (required): The ID of the Google sheet, which is the long string in the URL of the page
• clientEmail (required): The authorized client_email for your service account (remember to add permissions for this email to your sheet!)
• privateKey (required): the authorized private_key for your service account
• dateTimeFormat: An optional override of the date format that will return

Usage

const {getRows} = require("simple-sheets-reader");
const PRIVATE_KEY = "-----BEGIN PRIVATE KEY-----\nMIIEAoIBAQCwmz3cj...ee+Z81xUH4QTo18s=\n-----END PRIVATE KEY-----\n";

getRows([
    label: "people",
    range: "A2:B",
    mapping: ["firstName", "lastName"]
},{
    label: "locations",
    range: "'Cities'!C2:C",
    mapping: ["city"]
], {
    spreadsheetId: "9wLECuzvVpx8z7Ux5_9if_wdTDwhxXRcJZpJ-xhVeJRs",
    clientEmail: "[email protected]",
    privateKey: PRIVATE_KEY,
    dateTimeFormat: "FORMATTED_STRING" // Default value, can be overridden to "SERIAL_NUMBER"
}).then(response => {
    /*
    {
        people: [{
            firstName: "Kyle",
            lastName: "Coberly"
        },{
            firstName: "Elyse",
            lastName: "Coberly"
        }],
        cities: [{
            city: "Denver"
        },{
            city: "Seattle"
        }]
    }
    */
}).catch(console.error);

updateRows

updateRows(data, options).then();

data is an array of objects, following the following format:

[{
    range: "A2:A",
    values: [
        ["A"],
        ["B"]
    ]
}]

options include:

• spreadsheetId (required): The ID of the Google sheet, which is the long string in the URL of the page
• clientEmail (required): The authorized client_email for your service account (remember to add permissions for this email to your sheet!)
• privateKey (required): the authorized private_key for your service account
• valueInputOption: Whether input should be taken literally ("RAW"), or as if a user entered them ("USER_ENTERED", default)

It returns a count of modified rows.

Usage

const {updateRows} = require("simple-sheets");
const PRIVATE_KEY = "-----BEGIN PRIVATE KEY-----\nMIIEAoIBAQCwmz3cj...ee+Z81xUH4QTo18s=\n-----END PRIVATE KEY-----\n";

updateRows([{
    range: "A2:A",
    values: [["A"], ["B"]]
},{
    range: "Users!A2:B",
    values: [["C", "D"], ["E", "F"]]
}], {
    spreadsheetId: "9wLECuzvVpx8z7Ux5_9if_wdTDwhxXRcJZpJ-xhVeJRs",
    clientEmail: "[email protected]",
    privateKey: PRIVATE_KEY
}).then(response => {
    /*
    {
        updatedRows: 4
    }
    */
}).catch(console.error);

addRows

addRows(range, data, options);

• range is an A1 range (eg "A2:A") that will be searched to find something table-like to append to the end of.
• data is an array of arrays of values to add:

[
    ["column 1", "column 2"],
    ["column 1", "column 2"]
]

options include:

• spreadsheetId (required): The ID of the Google sheet, which is the long string in the URL of the page
• clientEmail (required): The authorized client_email for your service account (remember to add permissions for this email to your sheet!)
• privateKey (required): the authorized private_key for your service account
• valueInputOption: Whether input should be taken literally ("RAW"), or as if a user entered them ("USER_ENTERED", default)

It returns an object with the count of modified rows:

Usage

const {addRows} = require("simple-sheets");
const PRIVATE_KEY = "-----BEGIN PRIVATE KEY-----\nMIIEAoIBAQCwmz3cj...ee+Z81xUH4QTo18s=\n-----END PRIVATE KEY-----\n";

addRows("'Form Responses'!A2:B", [
    ["column 1", "column 2"],
    ["column 1", "column 2"]
], {
    spreadsheetId: "9wLECuzvVpx8z7Ux5_9if_wdTDwhxXRcJZpJ-xhVeJRs",
    clientEmail: "[email protected]",
    privateKey: PRIVATE_KEY
}).then(response => {
    /*
    {
        updatedRows: 2
    }
    */
}).catch(console.error);

clearSheets

clearSheets(sheets, options).then();

sheets is an array of sheet names, following the following format:

["First Sheet Name", "Second"]

options include:

• spreadsheetId (required): The ID of the Google sheet, which is the long string in the URL of the page
• clientEmail (required): The authorized client_email for your service account (remember to add permissions for this email to your sheet!)
• privateKey (required): the authorized private_key for your service account

It returns an array containing the counts of modified rows in each sheet.

Usage

const {clearSheets} = require("simple-sheets");
const PRIVATE_KEY = "-----BEGIN PRIVATE KEY-----\nMIIEAoIBAQCwmz3cj...ee+Z81xUH4QTo18s=\n-----END PRIVATE KEY-----\n";

clearSheets(["First Sheet Name", "Second"], {
    spreadsheetId: "9wLECuzvVpx8z7Ux5_9if_wdTDwhxXRcJZpJ-xhVeJRs",
    clientEmail: "[email protected]",
    privateKey: PRIVATE_KEY
}).then(response => {
    /*
    [{
        updatedRows: 4
    },{
        updatedRows: 2
    }]
    */
}).catch(console.error);

seedSheets

seedSheets(sheets, options).then();

sheets is an array of sheet objects, following the following format:

[{
    sheetName: "Sheet 1",
    mapping: ["column1", "column2"],
    seedData: [{
        column1: "a",
        column2: "b",
    },{
        column1: "c",
        column2: "d",
    }]
},{
    sheetName: "Sheet 2",
    mapping: ["column1", "optionalColumn"],
    seedData: [{
        column1: "e",
        optionalColumn: "f"
    },{
        column1: "g"
    }]
}]

Please note the following:

• This method assumes the first row is headers
• The mapping must include all column labels, in order
• The seed columns can be entered in any order (and can even be omitted), but must have a matching mapping value

options include:

• spreadsheetId (required): The ID of the Google sheet, which is the long string in the URL of the page
• clientEmail (required): The authorized client_email for your service account (remember to add permissions for this email to your sheet!)
• privateKey (required): the authorized private_key for your service account

It returns an array containing the counts of modified rows in each sheet.

Usage

const {clearSheets} = require("simple-sheets");
const PRIVATE_KEY = "-----BEGIN PRIVATE KEY-----\nMIIEAoIBAQCwmz3cj...ee+Z81xUH4QTo18s=\n-----END PRIVATE KEY-----\n";

seedSheets([{
    sheetName: "Sheet 1",
    mapping: ["column1", "column2"],
    seedData: [{
        column1: "a",
        column2: "b",
    },{
        column1: "c",
        column2: "d",
    }]
},{
    sheetName: "Sheet 2",
    mapping: ["column1", "optionalColumn"],
    seedData: [{
        column1: "e",
        optionalColumn: "f"
    },{
        column1: "g"
    }]
}],{
    spreadsheetId: "9wLECuzvVpx8z7Ux5_9if_wdTDwhxXRcJZpJ-xhVeJRs",
    clientEmail: "[email protected]",
    privateKey: PRIVATE_KEY
}).then(response => {
    /*
    [{
        updatedRows: 2
    },{
        updatedRows: 2
    }]
    */
}).catch(console.error);

Look at the Google Sheets batchGet API Docs, the Google Sheets batchUpdate API Docs and the Google Sheets append API Docs for more information.

Testing

npm test