SimpleGoogleSheetsJS

Create, read, and write Google Sheets through a simplified interface for the Google Sheets API

Installation

npm install simplegooglesheetsjs

Usage

``
const simplegooglesheets = require("./index.js");


let spreadsheet = new simplegooglesheets(); //Initialize the spreadsheet object

//Next, authorize the spreadsheet access
spreadsheet.authorizeAPIKey("MY_API_KEY");
//Or, authorize with a service account
spreadsheet.authorizeServiceAccount("CLIENT_EMAIL", "PRIVATE_KEY");
//Or, authorize with a JSON key file (containing fields client_email and private_key)
spreadsheet.authorizeServiceAccountFile("/path/to/key/file.json");


//Use an existing spreadsheet
const spreadsheetId = "MY_SHEET_ID";
spreadsheet.setSpreadsheet(spreadsheetId).then(() => {
spreadsheet.setSheet("MY_SHEET_NAME").then(() => {
//Print out the headers
console.log(spreadsheet.getHeaders().Headers);

//Get and print a row by header names
spreadsheet.getRow(5).then((row) => console.log(row));
});
});

//Or create a new one
spreadsheet.createSpreadsheet("My new spreadsheet");

//Get rows
spreadsheet.getRowArray(2).then((res) => console.log(res))
spreadsheet.getRows(2,10).then((rows) => console.log(rows));
spreadsheet.getLastRow().then((i) => { //Get the last row index, then get the last row
spreadsheet.getRow(i).then((data) => {
console.log(data);
});
});

//Add a row to the end
spreadsheet.addRow({"HEADER1":"DATA1"});
spreadsheet.addRowArray(["DATA1","DATA2","DATA3"]);

//Set a row based on index
spreadsheet.setRow(2, {"HEADER1":"DATA1"});
spreadsheet.setRowArray(2, ["DATA1","DATA2","DATA3"]);

//Delete a row
spreadsheet.deleteRow(2);
``

Documentation

simplegooglesheetsjs

Requires: module:googleapis

* simplegooglesheetsjs
* .SimpleGoogleSheets
* new SimpleGoogleSheets()
* ~authorizeServiceAccount(client_email, private_key)
* ~authorizeServiceAccountFile(keyFile)
* ~authorizeAPIKey(key)
* ~refreshHeaders() ⇒ Promise
* ~setRowArray(rowIndex, dataArray) ⇒ Promise
* ~setRow(rowIndex, data) ⇒ Promise
* ~addRow(rowIndex, data) ⇒ Promise
* ~addRowArray(rowIndex, dataArray) ⇒ Promise
* ~getRowArray(rowIndex) ⇒ Promise.<Array>
* ~getRow(rowIndex) ⇒ Promise.<Object>
* ~getLastRowIndex() ⇒ Promise.<number>
* ~getRows(rowIndexStart, rowIndexEnd) ⇒ Promise.<Array.<Object>>
* ~setHeaders(headers) ⇒ Promise
* ~createAndSetSpreadsheet(name) ⇒ Promise
* ~createSheet(name) ⇒ Promise
* ~deleteSheet(nameOrId) ⇒ Promise
* ~deleteRow(rowIndex) ⇒ Promise
* ~deleteCol(colIndex) ⇒ Promise
* ~findAndReplace(find, replacement, allSheets, matchCase, entireCell, useRegex) ⇒ Promise
* ~setSheet(nameOrId) ⇒ Promise
* ~setSpreadsheet(spreadsheetId) ⇒ Promise
* ~metadata() ⇒ Object
* ~getColumnName(columnIndex) ⇒
* ~getRangeName(rowStart, columnStart, rowEnd, columnEnd) ⇒ string

$3

SimpleGoogleSheets is a simplified wrapper for the Google Sheets API

Kind: static class of simplegooglesheetsjs

* .SimpleGoogleSheets
* new SimpleGoogleSheets()
* ~authorizeServiceAccount(client_email, private_key)
* ~authorizeServiceAccountFile(keyFile)
* ~authorizeAPIKey(key)
* ~refreshHeaders() ⇒ Promise
* ~setRowArray(rowIndex, dataArray) ⇒ Promise
* ~setRow(rowIndex, data) ⇒ Promise
* ~addRow(rowIndex, data) ⇒ Promise
* ~addRowArray(rowIndex, dataArray) ⇒ Promise
* ~getRowArray(rowIndex) ⇒ Promise.<Array>
* ~getRow(rowIndex) ⇒ Promise.<Object>
* ~getLastRowIndex() ⇒ Promise.<number>
* ~getRows(rowIndexStart, rowIndexEnd) ⇒ Promise.<Array.<Object>>
* ~setHeaders(headers) ⇒ Promise
* ~createAndSetSpreadsheet(name) ⇒ Promise
* ~createSheet(name) ⇒ Promise
* ~deleteSheet(nameOrId) ⇒ Promise
* ~deleteRow(rowIndex) ⇒ Promise
* ~deleteCol(colIndex) ⇒ Promise
* ~findAndReplace(find, replacement, allSheets, matchCase, entireCell, useRegex) ⇒ Promise
* ~setSheet(nameOrId) ⇒ Promise
* ~setSpreadsheet(spreadsheetId) ⇒ Promise
* ~metadata() ⇒ Object
* ~getColumnName(columnIndex) ⇒
* ~getRangeName(rowStart, columnStart, rowEnd, columnEnd) ⇒ string



#### new SimpleGoogleSheets()
SimpleGoogleSheets default constructor



#### SimpleGoogleSheets~authorizeServiceAccount(client_email, private_key)
Authorize with a service account from an email and a private key

Kind: inner method of SimpleGoogleSheets

| Param | Type | Description |
| --- | --- | --- |
| client_email | string | The service account email |
| private_key | string | The service account private key |



#### SimpleGoogleSheets~authorizeServiceAccountFile(keyFile)
Authorize with a service account from a file

Kind: inner method of SimpleGoogleSheets

| Param | Type | Description |
| --- | --- | --- |
| keyFile | string | Name of the file to get the client_email and private_key from |



#### SimpleGoogleSheets~authorizeAPIKey(key)
Authorize with an API Key

Kind: inner method of SimpleGoogleSheets

| Param | Type | Description |
| --- | --- | --- |
| key | string | API Key |



#### SimpleGoogleSheets~refreshHeaders() ⇒ Promise
Refresh the current spreadsheet headers

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Promise of status


#### SimpleGoogleSheets~setRowArray(rowIndex, dataArray) ⇒ Promise
Set the row with the data in the array. Warning: will overwrite any headers (on row 0)

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Status of function

| Param | Type | Description |
| --- | --- | --- |
| rowIndex | number | Index of row |
| dataArray | Array.<string> | Data to set in cells (index=column number) |



#### SimpleGoogleSheets~setRow(rowIndex, data) ⇒ Promise
Set the row with the data in the format {"HEADER_1":"data1", "HEADER_N":"datan"}

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Status of function

| Param | Type | Description |
| --- | --- | --- |
| rowIndex | number | Index of row |
| data | HeaderData | Data to set in cells (in HEADER_NAME:VALUE pairs) |



#### SimpleGoogleSheets~addRow(rowIndex, data) ⇒ Promise
Add the row with the data in the format {"HEADER_1":"data1", "HEADER_N":"datan"}

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Status of function

| Param | Type | Description |
| --- | --- | --- |
| rowIndex | number | Index of row |
| data | HeaderData | Data to set in cells (in HEADER_NAME:VALUE pairs) |



#### SimpleGoogleSheets~addRowArray(rowIndex, dataArray) ⇒ Promise
Add the row with the data in the array. Warning: will overwrite any headers (on row 0)

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Status of function

| Param | Type | Description |
| --- | --- | --- |
| rowIndex | number | Index of row |
| dataArray | Array.<string> | Data to set in cells (index=column number) |



#### SimpleGoogleSheets~getRowArray(rowIndex) ⇒ Promise.<Array>
Get the row and return the data in the array

Kind: inner method of SimpleGoogleSheets
Returns: Promise.<Array> - Array of data

| Param | Type | Description |
| --- | --- | --- |
| rowIndex | number | Index of row |



#### SimpleGoogleSheets~getRow(rowIndex) ⇒ Promise.<Object>
Get the row and return the data in the format {"HEADER_1":"data1", "HEADER_N":"datan"}

Kind: inner method of SimpleGoogleSheets
Returns: Promise.<Object> - Data

| Param | Type | Description |
| --- | --- | --- |
| rowIndex | number | Index of row |



#### SimpleGoogleSheets~getLastRowIndex() ⇒ Promise.<number>
Get the index of the last row

Kind: inner method of SimpleGoogleSheets
Returns: Promise.<number> - Index of the last row with data


#### SimpleGoogleSheets~getRows(rowIndexStart, rowIndexEnd) ⇒ Promise.<Array.<Object>>
Get an array of rows and return the data in the format [{"HEADER_1":"data1", "HEADER_N":"datan"}]

Kind: inner method of SimpleGoogleSheets
Returns: Promise.<Array.<Object>> - Data

| Param | Type | Description |
| --- | --- | --- |
| rowIndexStart | number | Index of row start |
| rowIndexEnd | number | Index of row start |



#### SimpleGoogleSheets~setHeaders(headers) ⇒ Promise
Set the spreadsheet headers

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Promise of update status

| Param | Type | Description |
| --- | --- | --- |
| headers | Headers | Headers to set |



#### SimpleGoogleSheets~createAndSetSpreadsheet(name) ⇒ Promise
Create a new spreadsheet and set it as current

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Undefined on success

| Param | Type |
| --- | --- |
| name | string |



#### SimpleGoogleSheets~createSheet(name) ⇒ Promise
Create and set as current sheet

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Undefined on success

| Param | Type | Description |
| --- | --- | --- |
| name | string | String name of sheet |



#### SimpleGoogleSheets~deleteSheet(nameOrId) ⇒ Promise
Delete the sheet referenced by the name or the ID

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Undefined on success

| Param | Type | Description |
| --- | --- | --- |
| nameOrId | string \| number | Integer ID or string name of sheet |



#### SimpleGoogleSheets~deleteRow(rowIndex) ⇒ Promise
Delete the row in the spreadsheet. Warning: deleting row 0 will remove the spreadsheet headers (if any)

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Undefined on success

| Param | Type | Description |
| --- | --- | --- |
| rowIndex | number | Index of row to delete |



#### SimpleGoogleSheets~deleteCol(colIndex) ⇒ Promise
Delete the column in the spreadsheet

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Undefined on success

| Param | Type | Description |
| --- | --- | --- |
| colIndex | string | Index of column to delete |



#### SimpleGoogleSheets~findAndReplace(find, replacement, allSheets, matchCase, entireCell, useRegex) ⇒ Promise
Find and replace the data in the spreadsheet

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Promise of number of values replaced

| Param | Type | Description |
| --- | --- | --- |
| find | string | The string to search for |
| replacement | string | Replacement string for find and replace |
| allSheets | boolean | Search all sheets or just the current one |
| matchCase | boolean | Match the case |
| entireCell | boolean | Match the entire cell |
| useRegex | boolean | Use regex to search the cell (find must be a regex string) |



#### SimpleGoogleSheets~setSheet(nameOrId) ⇒ Promise
Set the current sheet, referenced by the name or the id. Also updates the headers

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Undefined on success

| Param | Type | Description |
| --- | --- | --- |
| nameOrId | string \| number | Integer id or string name of sheet |



#### SimpleGoogleSheets~setSpreadsheet(spreadsheetId) ⇒ Promise
Set the current spreadsheet

Kind: inner method of SimpleGoogleSheets
Returns: Promise - Undefined on success

| Param | Type | Description |
| --- | --- | --- |
| spreadsheetId | string | ID of spreadsheet to use |



#### SimpleGoogleSheets~metadata() ⇒ Object
Get the current spreadsheet meta data

Kind: inner method of SimpleGoogleSheets
Returns: Object - Metadata of current spreadsheet


#### SimpleGoogleSheets~getColumnName(columnIndex) ⇒
Get the name of the column (such as A, ABZ, or ZAD) from the index

Kind: inner method of SimpleGoogleSheets
Returns: Column name (such as ABZ)
Throws:

- Error if invalid index provided


| Param | Type | Description |
| --- | --- | --- |
| columnIndex | number | Get the column name from the index |



#### SimpleGoogleSheets~getRangeName(rowStart, columnStart, rowEnd, columnEnd) ⇒ string
Get the name of the range (in sheets format) from indexes

Kind: inner method of SimpleGoogleSheets
Returns: string - Range
Throws:

- Error if invalid values provided


| Param | Type | Description |
| --- | --- | --- |
| rowStart | number | Starting row (and/or columnStart) |
| columnStart | number | Starting column |
| rowEnd | number | Ending row (and/or columnEnd) |
| columnEnd | number | Ending column |

License

Copyright 2020 Alex Mous

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.