Skip to main content

Google Spreadsheet

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

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