Google Spreadsheet demo

Doc

NPM : https://www.npmjs.com/package/google-spreadsheet

Doc: https://theoephraim.github.io/node-google-spreadsheet/#/getting-started/authentication

Google sheet API doc: https://developers.google.com/sheets/api/quickstart/js?hl=fr

Prerequisites

👉 Activate your Sheet API 👈

Go to the Google Developers Console

• Select your project or create a new one (and then select it)
• Enable the Sheets API for your project
• In the sidebar on the left, select APIs & Services > Library
• Search for "sheets"
• Click on "Google Sheets API"
• click the blue "Enable" button

Setup Instructions for service account Follow steps above to set up project and enable sheets API

1. Create a service account for your project

• In the sidebar on the left, select APIs & Services > Credentials
• Click blue "+ CREATE CREDENTIALS" and select "Service account" option
• Enter name, description, click "CREATE"
• You can skip permissions, click "CONTINUE"

2. Click "+ CREATE KEY" button

• Select the "JSON" key type option
• Click "Create" button
• your JSON key file is generated and downloaded to your machine (it is the only copy!)
• click "DONE"
• note your service account's email address (also available in the JSON key file)

3. Share the doc (or docs) with your service account using the email noted above

• Open the service_account.json file and find the client_email property.
• On your Google Spreadsheet, click on the Share button in the top right, and add the email address of the service account as an editor.

Installation

Open zenv and copy your key in folder

open .

Install NPM dependency

npm i google-spreadsheet --save

Manipulate your Spreadsheet

const { GoogleSpreadsheet } = require('google-spreadsheet');
const creds = require('./myapp-spreadsheet.json');

    
// Initialize the sheet - doc ID is the long id in the sheets URL
const doc = new GoogleSpreadsheet('YOUR_SPREADSHEET_ID');
await doc.useServiceAccountAuth(creds);

await doc.loadInfo(); // loads document properties and worksheets
console.log(doc.title);
await doc.updateProperties({ title: 'renamed doc' });

const sheet = doc.sheetsByIndex[0]; // or use doc.sheetsById[id] or doc.sheetsByTitle[title]
console.log(sheet.title);
console.log(sheet.rowCount);

// adding / removing sheets
const newSheet = await doc.addSheet({ title: 'hot new sheet!' });
//await newSheet.delete();