Получение данных из Google sheets

npm install googleapis

--- config.json
{
  "spreadsheetId": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
}


--- credentials.json
{
  "type": "service_account",
  "project_id": "flamap-ee39b",
  "private_key_id": "XXXXXXXXXXXXXXXXXXXXXX",
  "private_key": "-----BEGIN PRIVATE KEY-----
   ....
   -----END PRIVATE KEY-----\n",
  "client_email": "[email protected]",
  "client_id": "XXXXXXXXXXXXXXXXXXXXXXXXXXX",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/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/test-gsheet%40flamap-ee00b.iam.gserviceaccount.com"
}


--- googleAuth.js
const fs = require('fs');
const path = require('path');
const { promisify } = require('util');
const { google } = require('googleapis');
const readFile = promisify( fs.readFile );
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const CREDENTIALS_PATH = path.join( __dirname, 'credentials.json' );

const getAuthClient = async () => {
    const content = await readFile(CREDENTIALS_PATH)
        .catch( error => console.log('Error loading client secret file:', error ));

    const { client_email, private_key } = JSON.parse(content);

    const client = new google.auth.JWT(
        client_email,
        null,
        private_key,
        SCOPES,
        null,
    );

    return client;
};

module.exports = {
    getAuthClient
};


--- index.js
const { google }        = require('googleapis');
const { getAuthClient } = require('./googleAuth');
const config            = require('./config.json');

const getApiClient = async () => {
    const authClient = await getAuthClient();
    const { spreadsheets: apiClient } = google.sheets({
        version: 'v4',
        auth   : authClient,
    });
    return apiClient;
};

const getValuesData = async (apiClient, range) => {
    const { data } = await apiClient.get({
        spreadsheetId  : config.spreadsheetId,
        ranges         : range,
        fields         : 'sheets',
        includeGridData: true,
    });
    return data.sheets;
};

const getDataFromGoogleSheet = async () => {
    const range = 'A1:C2';
    const apiClient = await getApiClient();
    const [sheet] = await getValuesData(apiClient, range);
    console.log(sheet);
    console.log(sheet.data[0].rowMetadata, sheet.data[0].columnMetadata);

    for (let row of sheet.data[0].rowData) {
        console.log(row);
    }
};

getDataFromGoogleSheet();