google-sheets-uploader

An unofficial client intended to download AWS S3 object conatining CSV/TSV file and subsequently insert its conent to specified Google Sheets spreadsheet.

Table of contents

• Getting Started
• Functions
  • authorizeGoogle(secretsPath, tokenPath)
  • downloadS3Data(bucketName, objectKey, dataEncoding)
  • processS3Data(downloadedData, dataSepratator)
  • appendGoogleSheet(version, auth, id, range, inputOption, resource)
  • clearAndUpdateGoogleSheet(version, auth, id, range, inputOption, resource)
  • updateGoogleSheet(version, auth, id, range, inputOption, resource)
• Usage
• Changelog
• License

Getting Started

1. Install module

   npm install @danse4mobility/google-sheets-uploader

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

downloadS3Data(bucketName, objectKey, dataEncoding)

Downloads specified S3 object containing CSV/TSV file

• @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} dataEncoding Data encoding to convert the S3 object body from a Buffer to a String - e.g. "utf-8", "base64", "ascii"
• @returns {String} String representing data from downloaded CSV/TSV file

processS3Data(downloadedData, dataSepratator)

Processes downloaded CSV/TSV data for proper representation for Google Sheets

• @param {String} downloadedData String representing data from downloaded CSV/TSV file
• @param {String} dataSepratator Name of the separator that is used to separate values in the CSV/TSV file - can be only "comma" or "tab"
• @returns {any} Processed data that are prepared to be written to Google Sheets

appendGoogleSheet(version, auth, id, range, inputOption, resource)

Appends processed data to the specified 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
• @param {String} inputOption How the input data should be interpreted - can be only "INPUT_VALUE_OPTION_UNSPECIFIED", "RAW", or "USER_ENTERED"
• @param {any} resource The processed data that are prepared to be written to the spreadsheet
• @returns {any} The append values response

clearAndUpdateGoogleSheet(version, auth, id, range, inputOption, resource)

Clears the whole spreadsheet and subsequently inserts processed data to the 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
• @param {String} inputOption How the input data should be interpreted - can be only "INPUT_VALUE_OPTION_UNSPECIFIED", "RAW", or "USER_ENTERED"
• @param {any} resource The processed data that are prepared to be written to the spreadsheet
• @returns {any} The clear or update values response

updateGoogleSheet(version, auth, id, range, inputOption, resource)

Updates spreadsheet with the processed data

• @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
• @param {String} inputOption How the input data should be interpreted - can be only "INPUT_VALUE_OPTION_UNSPECIFIED", "RAW", or "USER_ENTERED"
• @param {any} resource The processed data that are prepared to be written to the spreadsheet
• @returns {any} The update values response

Usage

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

const secretsPath = 'client_secret.json';
const tokenPath = 'token.json';
const bucketName = "sheetstack-mybucket123a-1123";
const objectKey = "data.csv";
const encoding = "utf-8";
const dataSeparator = "comma"; // supported values are "comma" or "tab"
const version = "v4";
const id = "17Ea_eD4e63OKPFrRtjtwPMh7seIqc6JUF1HsHrCpul";
const range = "A1:K";
const inputOption = "RAW"; // supported values are "INPUT_VALUE_OPTION_UNSPECIFIED", "RAW" or "USER_ENTERED"


var oAuth2Client = await sheetsUploader.authorizeGoogle(secretsPath, tokenPath);
var downloadedData = await sheetsUploader.downloadS3Data(bucketName, objectKey, encoding);
var resource = await sheetsUploader.processS3Data(downloadedData, dataSepratator);
var response = await sheetsUploader.clearAndUpdateGoogleSheet(version, oAuth2Client, id, range, inputOption, resource);
console.log(response);
```

Changelog

• 0.1.0: Initial release

License

Module is MIT -licensed