Skip to main content

Google Spreadsheet

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


Credential configuration

To configure this credential you need to:

  • Enable the Google Sheets API for your project in Google Cloud.

  • Obtain the spreadsheet ID: You can find it in the URL

  • Obtain the JSON key of a service account. This key is downloaded as a JSON file when you create it. It should look 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": "",
"token_uri": "",
"auth_provider_x509_cert_url": "",
"client_x509_cert_url": ""

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 ensure proper functionality.

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

Google Spreadsheet Snippets available in Editor


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


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

const googleSpreadsheetServiceAccountJson = {
type: "service_account",
project_id: "yepcode",
private_key_id: "XXXXX",
private_key: \`-----BEGIN PRIVATE KEY-----\nx\n-----END PRIVATE KEY-----\`,
client_email: "",
client_id: "1234567890",
auth_uri: "",
token_uri: "",
auth_provider_x509_cert_url: "",
client_x509_cert_url: "",

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

await googleSpreadsheetClient.useServiceAccountAuth(googleSpreadsheetServiceAccountJson);

Load Document Properties and Worksheets

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

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();