CODEWITHSUNDEEP
javascriptgoogle-sheetsgoogle-cloud-platformgoogle-apigoogle-sheets-api
Max888
Max888

Reputation: 3780

Google sheets API minimal example

I am using the google-spreadsheet npm package to fetch data from the first 2 tabs of a Google Sheet which has been shared with a service account. Since this is a simple task, I would like to remove the dependency on a third party package and use Google's libraries directly. However, there are many different Google libraries and I am not sure what to use.

Below is the google-spreadsheet based code I am currently using. How can I convert this to directly use Google libraries?

var GoogleSpreadsheet = require('google-spreadsheet');
var creds = require('./credentials.json');

var doc = new GoogleSpreadsheet('### spreadsheet id ###');

doc.useServiceAccountAuth(creds, function(err) {
    doc.getRows(1, function(err, rows) {
        console.log(rows);
    });
    doc.getRows(2, function(err, rows) {
        console.log(rows);
    });
});

Upvotes: 0

Views: 1760

Answers (2)

Alex Baban
Alex Baban

Reputation: 11732

The code below works with Google's library googleapis refereed in Google's "Node.js Quickstart" (https://developers.google.com/drive/api/v3/quickstart/nodejs).

You can install the client library with npm install googleapis --save

Google Sheets API reference (https://developers.google.com/sheets/api/reference/rest)

You could start with something like this:

const { google } = require('googleapis');

const creds = require('./credentials.json');

// ID of the Google Sheets spreadsheet to retrieve data from
const spreadsheetId = '### spreadsheet id ###';

// Name of the sheet tab
const sheetName = 'Sheet1';

const authClient = new google.auth.JWT(
    creds.client_email,
    null,
    creds.private_key,
    ['https://www.googleapis.com/auth/spreadsheets.readonly']);

const sheets = google.sheets({ version: 'v4', auth: authClient });

sheets.spreadsheets.values.batchGet({
    spreadsheetId: spreadsheetId,
    // A1 notation of the values to retrieve  
    ranges: [sheetName + '!A:Z'],
    majorDimension: 'ROWS'
})
    .then((resp) => {
        const rangesOfValues = resp.data.valueRanges;
        rangesOfValues.forEach((range) => {
            console.log(range);
        });
    })
    .catch((err) => {
        console.log(err);
    });


// authClient.authorize()
//     .then(() => {
//         console.log("Authorize test passed...");
//     })
//     .catch((err) => {
//         console.log(err);
//     });

Let me know if that helps at all.

Upvotes: 0

Iamblichus
Iamblichus

Reputation: 19339

This is Google's officially supported Node.js client library for accessing Google APIs. This covers all types of authentication, including using a Service Account. I assume this is what you're looking for.

Also, in Google Sheets API official documentation you can find many examples of this library in use.

On one side, there is a quickstart in which it is explained, step by step, how to set your application to make a call (in the example, it gets the values from a specified range in a spreadsheet, which, if I understood correctly, is what you want to do).

On the other side, you can find examples of this library in use for most of this API methods in the reference tab.

I hope this is of any help.

Upvotes: 1

Related Questions