Google Sheets Manager (TypeScript)
Google Sheets API v4 abstracted TypeScript modules for reading and manipulating Google Spreadsheets using service account access.
Modules (WIKI)
google-sheets-manager wiki)
(Modules and code components breakdown and documentation on the wiki
Installation
Basic Usage
Simple example to show some of the things you can do.
var googlesheets = ;var creds = ; var ServiceAccount = googlesheetsServiceAccount;var GoogleSheet = googlesheetsGoogleSheet; const GOOGLE_SPREADSHEETID = ''; // update placeholder valueconst GOOGLE_SHEETID = 0; // update placeholder value var authClass = creds;var sheetAPI = authClass GOOGLE_SPREADSHEETID GOOGLE_SHEETID; var { if err throw err; console;}; sheetAPI; sheetAPI; sheetAPI; sheetAPI; sheetAPI; sheetAPI;
Authentication
Service Account (recommended method)
This is a 2-legged oauth method and designed to be "an account that belongs to your application instead of to an individual end user". Use this for an app that needs to access a set of documents that you have full access to. (read more)
Setup Instructions
- 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, expand APIs & auth > APIs
- Search for "sheets"
- Click on "Sheets API"
- click the blue "Enable API" button
- Create a service account for your project
- In the sidebar on the left, expand APIs & auth > Credentials
- Click blue "Add credentials" button
- Select the "Service account" option
- Select "Furnish a new private key" checkbox
- Select the "JSON" key type option
- Click blue "Create" button
- your JSON key file is generated and downloaded to your machine (it is the only copy!)
- note your service account's email address (also available in the JSON key file)
- Share the doc (or docs) with your service account using the email noted above
API
This module follows "normal" node callback conventions:
- Every method that takes a callback takes it as its last param
- Every callback will be called with the error (or null) as first param
- Some methods have optional params
GoogleSpreadsheet
TODO
ServiceAccount
Wrapper class for simplifing authentication process using service account method
new ServiceAccount(creds, [googleAuthScopes])
creds
object - service account credentials loaded as json object (see setup instruction above)googleAuthScopes
string[] - google scopes which you want to give the api access on, default value is scopes (["https://www.googleapis.com/auth/spreadsheets"])
GoogleSheet
Represents a single "sheet" from the spreadsheet. These are the different tabs/pages visible at the bottom of the Google Sheets interface.
sheets info returned as property sheets
when calling GoogleSheet.getInfo
.
Properties:
spreadsheetId
number - the ID of the main spreadsheet, could be extracted from sheet's url using this regex/spreadsheets/d/([a-zA-Z0-9-_]+)
sheetId
number - the ID of the single sheet tab, could be extracted from sheet's url using this regex[#&]gid=([0-9]+)
GoogleSheet(authClass, spreadsheetId, [sheetId])
new - `authClass AuthClass - instance of AuthClass inhereted class, like ServiceAccount
spreadsheetId
number - the ID of the main spreadsheetsheetId
number - the ID of the single sheet tab, default value is 0
SpreadsheetWorksheet.getData([options], callback)
Loads data from google sheet.
options
object (optional)range
(optional) - The range of the data to be fetched, if not provided then the function returns all the data in the sheet.startRow
number (optional) - default: 1startCol
number (optional) - default: 1endRow
number (optional) - default: MAX_ROWendCol
number (optional) - default: MAX_COL
majorDimension
string - enum('ROWS' || 'COLUMNS') how the loaded data should be represented, either array of rows or array of columns, default values is 'ROWS'
callback
(err, res)res
two dimentional array containing the range data
SpreadsheetWorksheet.getBatchData([options], callback)
Loads data from multiple ranges in a single request from google sheet.
options
object (optional)ranges
object Array (optional) - The ranges of the data to be fetched, if not provided then the function returns all the data in the sheet.startRow
number (optional) - default: 1startCol
number (optional) - default: 1endRow
number (optional) - default: MAX_ROWendCol
number (optional) - default: MAX_COL
majorDimension
string - enum('ROWS' || 'COLUMNS') how the loaded data should be represented, either array of rows or array of columns, default values is 'ROWS'
callback
(err, res)res
two dimentional array containing the range data
SpreadsheetWorksheet.setData(data, [options], callback)
set data in google sheet.
data
(string | null | undefined) 2D array - representing data to be updated to the google sheetoptions
object (optional)range
object (optional) - The range of the data to be updated, if not provided then the function apply the data to the first range that fits in the sheet.startRow
number (optional) - default: 1startCol
number (optional) - default: 1endRow
number (optional) - default: MAX_ROWendCol
number (optional) - default: MAX_COL
majorDimension
string - enum('ROWS' || 'COLUMNS') how the provided data are represented, either array of rows or array of columns, default values is 'ROWS'
callback
(err, res)res
contains google reponse with the updated cells info
SpreadsheetWorksheet.setBatchData(data, [options], callback)
set data in multiple ranges in a single request from google sheet.
data
(string | null | undefined) 3D array - representing array of data to be updated to the google sheetoptions
object (optional)ranges
object Array (optional) - The ranges of the data to be updated, if not provided then the function apply the data to the first range that fits in the sheet, If ranges provided then it must be of the same size as data array first dimension.startRow
number (optional) - default: 1startCol
number (optional) - default: 1endRow
number (optional) - default: MAX_ROWendCol
number (optional) - default: MAX_COL
majorDimension
string - enum('ROWS' || 'COLUMNS') how the provided data are represented, either array of rows or array of columns, default values is 'ROWS'
callback
(err, res)res
contains google reponse with the updated cells info
GoogleRow
TODO
GoogleCol
TODO
GoogleCell
TODO
Modules
- "api"
- "auth-classes/auth-class"
- "auth-classes/service-account"
- "common"
- "constants"
- "sheets/google-sheet"
- "utils/errors"
- "utils/type_alias"
- "utils/utils"