Simplegooglesheetsjs NPM

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

simplegooglesheetsjs.SimpleGoogleSheets

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

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.