Skip to main content

Google Spreadsheet

Google Spreadsheet is a wrapper that allows you to use the Google Sheets API

Official Websitehttps://github.com/theoephraim/node-google-spreadsheet
Documentationhttps://github.com/theoephraim/node-google-spreadsheet
NodeJS packagehttps://www.npmjs.com/package/google-spreadsheet
Version3.2.0
Source Codehttps://github.com/theoephraim/node-google-spreadsheet
TagsSpreadsheet, Saas

Credential configuration​

To configure this credential you need:

  • Have the Google Sheets API enabled for your project in Google Cloud.

  • The spreadsheet ID: You can obtain it from the URL https://docs.google.com/spreadsheets/d/----SPREADSHEET-ID----/edit

  • The JSON key of a service account. This key is downloaded as a JSON file when you create it. You only need to paste its content inside the JSON field in the form. If you don't have one, create a service account and then, a JSON key for that service account. You need to share the spreadsheet with the service account to make this work properly.

The JSON format looks like this:

{
  "type": "service_account",
  "project_id": "PROJECT_ID",
  "private_key_id": "KEY_ID",
  "private_key": "-----BEGIN PRIVATE KEY-----\nPRIVATE_KEY\n-----END PRIVATE KEY-----\n",
  "client_email": "SERVICE_ACCOUNT_EMAIL",
  "client_id": "CLIENT_ID",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/SERVICE_ACCOUNT_EMAIL"
}

Here you have an example of a filled credential configuration form in YepCode:

Google Spreadsheet snippets available in editor​

note

The title is the triggering text for YepCode to autocomplete the script

Integration​

New integration from credential
const googleSpreadsheetClient = await yepcode.integration.googleSpreadsheet("credential-slug");
New integration from plain authentication data
const { GoogleSpreadsheet } = require("google-spreadsheet");

const googleSpreadsheetCredentials = {
  projectId: "YepCode",
  credentials: {
    type: "service_account",
    project_id: "yepcode",
    private_key_id: "XXXXX",
    private_key: \`-----BEGIN PRIVATE KEY-----\nx\n-----END PRIVATE KEY-----\`,
    client_email: "yepcode@example.org",
    client_id: "1234567890",
    auth_uri: "https://example.org",
    token_uri: "https://example.org",
    auth_provider_x509_cert_url: "https://example.org",
    client_x509_cert_url: "https://example.org",
  }
};

const googleSpreadsheetClient = new GoogleSpreadsheet("spreadsheet-id");

await googleSpreadsheetClient.useServiceAccountAuth(googleSpreadsheetCredentials);

Load document properties and worksheets​

Load document
await googleSpreadsheetClient.loadInfo();
console.log(googleSpreadsheetClient.title);
const sheet = googleSpreadsheetClient.sheetsByTitle["sheetTitle"];
console.log(sheet.title);
console.log(sheet.rowCount);

Create new sheet and add rows​

Create new sheet and add rows
await googleSpreadsheetClient.loadInfo();
const sheet = await googleSpreadsheetClient.addSheet({ title: "sheetTitle", headerValues: ["columnName", "columnName"] });

await sheet.addRow({ "columnName": "value", "columnName": "value" });

Update and remove rows​

Update and remove rows
await googleSpreadsheetClient.loadInfo();
const sheet = googleSpreadsheetClient.sheetsByTitle["sheetTitle"];
const rows = await sheet.getRows();

rows[1].columnName = newValue;
await rows[1].save();
await rows[2].delete();

Work with cells​

Work with cells
await googleSpreadsheetClient.loadInfo();
const sheet = googleSpreadsheetClient.sheetsByTitle["sheetTitle"];
await sheet.loadCells("A1:E10");
console.log(sheet.cellStats); // total cells, loaded, how many non-empty
const a2 = sheet.getCell(1, 0);
const a3 = sheet.getCellByA1("A3");

a2.value = newValue
a3.value = newValue
await sheet.saveUpdatedCells();
Last updated on