google-sheets-downloader

An unofficial client intended to download the content of a Google Sheets spreadsheet and subsequently upload its content as an AWS S3 object.

Table of contents

• Getting Started
• Functions
  • authorizeGoogle(secretsPath, tokenPath)
  • getSpreadsheetData(version, auth, id)
  • downloadGoogleSheet(version, auth, id, range)
  • processGoogleSheetData(downloadedData, headerArrayLength)
  • uploadDataToS3(bucketName, objectKey, processedData)
• Usage
• Changelog
• License

Getting Started

1. Install module

   npm install @danse4mobility/google-sheets-downloader

2. Create a project in Google Developer Console, for example: "Google Sheets App"

3. Enable the Google Sheets API

4. Create credentials for the Google Sheets API and save the file credentials.json to your working directory

5. Share the Sheets document to service email address using the Share button

6. Pick up the Sheets document id from URL or Share dialog. Example:

   # Sheets document browser URL
   https://docs.google.com/spreadsheets/d/17Ea_eD4e63OKPFrRtjtwPMh7seIqc6JUF1HsHrCpul/edit#gid=0
   # Sheets document id
   17Ea_eD4e63OKPFrRtjtwPMh7seIqc6JUF1HsHrCpul

7. Let's start the work!

Functions

authorizeGoogle(secretsPath, tokenPath)

If the tokenPath contains the JSON file with token, reads the stored token and returns the authorized OAuth2 client. Otherwise, gets and creates new token in the destination determined by tokenPath after prompting for user authorization, and then returns authorized OAuth2 client.

• @param {String} secretsPath Path to the JSON formatted file containing the client ID, client secret, and other OAuth 2.0 parameters
• @param {String} tokenPath Path to the JSON formatted file containing a token or path to create it.
• @returns {google.auth.OAuth2} The authorized OAuth2 client

getSpreadsheetData(version, auth, id)

Gets information about a specific Google Spreadsheet

• @param {String} version Version of the google sheets API
• @param {oAuth2Client} auth The authorized OAuth2 client
• @param {String} id The ID of the spreadsheet to update
• @returns {any} Properties of the spreadsheet sheets

downloadGoogleSheet(version, auth, id, range)

Downloads specific spreadsheet

• @param {String} version Version of the google sheets API
• @param {oAuth2Client} auth The authorized OAuth2 client
• @param {String} id The ID of the spreadsheet to update
• @param {String} range The A1 notation of a range to search for a logical table of data. Values are appended after the last row of the table
• @returns {any} Downloaded data from the spreadsheet

processGoogleSheetData(downloadedData, headerArrayLength)

Processes downloaded data for proper representation in S3 storage

• @param {any} downloadedData The data that was read. This is an array of arrays, the outer array representing all the data and each inner array representing a major dimension. Each item in the inner array corresponds with one cell.
• @param {number} headerArrayLength Length of the array header
• @returns {any} Processed data that are prepared to be written to S3 storage

uploadDataToS3(bucketName, objectKey, processedData)

Uploads processed data to the specified S3 object

• @param {String} bucketName Name of the S3 bucket
• @param {String} objectKey Object key (or key name) that identifies the object in the bucket
• @param {String} processedData Processed data that are prepared to be written to S3 storage
• @returns {String} The location that identifies the newly created object

Usage

```javascript
const {sheetsDownloader} = require('@danse4mobility/google-sheets-downloader');

const secretsPath = 'client_secret.json';
const tokenPath = 'token.json';
const version = "v4";
const id = "17Ea_eD4e63OKPFrRtjtwPMh7seIqc6JUF1HsHrCpul";
const range = "A1:K";
const headerArrayLength = 7;
const bucketName = "sheetstack-mybucket123a-1123";
const objectKey = "data.csv";


var oAuth2Client = await sheetsDownloader.authorizeGoogle(secretsPath, tokenPath);
var downloadedData = await sheetsDownloader.downloadGoogleSheet(version, oAuth2Client, id, range);
var processedData = await sheetsDownloader.processGoogleSheetData(downloadedData, headerArrayLength);
var location = await sheetsDownloader.uploadDataToS3(bucketName, objectKey, processedData);
console.log(location);
```

Changelog

• 1.1.0: getSpreadsheetData method added
• 1.0.0: First version release
• 0.1.0: Initial release

License

Module is MIT -licensed