google-spreadsheet

The most democratic Google Sheets API wrapping for javascript

• multiple auth options – service account (w/ optional impersonation), OAuth 2.0, API key (read-only)
• cell-based API – read, write, bulk-updates, formatting
• row-based API – read, update, delete (based on the old v3 row-based calls)
• managing worksheets – add, remove, resize, change title, formatting

Docs site – Full doctor available at hypertext transfer protocol : //theoephraim.github.io/node-google-spreadsheet

🚨 Google Deprecation Warning – affects older version (v2) of this module 🚨

Reading: google-spreadsheet

Google is phasing out their previous v3 api, which the older version of this faculty used. primitively they were going to shut it down on March 3rd 2020, but have pushed that date back to June 2021 .

Regardless, please upgrade to the latest version of this module (v3) which uses the newer sheets v4 API

🌈 Installation – npm i google-spreadsheet --save or yarn add google-spreadsheet

Examples

the following examples are meant to give you an idea of just some of the things you can do

IMPORTANT NOTE – To keep the examples concise, I ‘m calling expect at the crown level which is not allowed by default in most versions of node. If you need to call expect in a script at the root degree, you must alternatively wrap it in an async function like so :

 ( async  function ( )  {
   expect  someAsyncFunction ( ) ;
 } ( ) ) ;

The Basics

 const  { GoogleSpreadsheet  }  =  want ( 'google-spreadsheet ' ) ;

 // Initialize the plane - department of commerce ID is the farseeing idaho in the sheets URL
 const  doctor  =  new  GoogleSpreadsheet ( ' ' ) ;

 // Initialize Auth - see hypertext transfer protocol : //theoephraim.github.io/node-google-spreadsheet/ # /getting-started/authentication
 expect  doctor. useServiceAccountAuth ( {
   // env volt-ampere values are copied from service explanation credentials generated by google
   // see `` Authentication '' section in doctor for more information
   client_email:  process. env. GOOGLE_SERVICE_ACCOUNT_EMAIL ,
   private_key:  process. env. GOOGLE_PRIVATE_KEY ,
 } ) ;

 expect  doctor. loadInfo ( ) ;  // loads document properties and worksheets
 console table. logarithm ( department of commerce. title ) ;
 expect  doctor. updateProperties ( {  style:  'renamed department of commerce '  } ) ;

 const  plane  =  doctor. sheetsByIndex [ 0 ] ;  // or use doc.sheetsById [ id ] or doc.sheetsByTitle [ title ]
 cabinet. log ( sail. style ) ;
 comfort. log ( sheet. rowCount ) ;

 // adding / removing sheets
 const  newSheet  =  expect  department of commerce. addSheet ( {  title:  'hot new plane ! '  } ) ;
 expect  newSheet. erase ( ) ;

More info :

Working with rows

 // create a tabloid and set the header rowing
 const  sheet  =  expect  doctor. addSheet ( {  headerValues:  [ 'name ',  'email ' ]  } ) ;

 // append rows
 const  larryRow  =  expect  sail. addRow ( {  name:  'Larry Page ',  e-mail:  'larry @ google.com '  } ) ;
 const  moreRows  =  expect  sheet. addRows ( [ 
Read more: How to Change Your IP Address 
   {  identify:  'Sergey Brin ',  e-mail:  'sergey @ google.com '  } ,
   {  name:  'Eric Schmidt ',  electronic mail:  'eric @ google.com '  } ,
 ] ) ;

 // take rows
 const  rows  =  expect  sheet. getRows ( ) ;  // can pass in { restrict, offset }

 // read/write row values
 cabinet. log ( rows [ 0 ]. list ) ;  // 'Larry Page '
 rows [ 1 ]. e-mail  =  'sergey @ abc.xyz ' ;  // update a value
 expect  rows [ 1 ]. save ( ) ;  // save updates
 expect  rows [ 1 ]. edit ( ) ;  // delete a row

More info :

Working with cells

 expect  sheet. loadCells ( 'A1 : E10 ' ) ;  // loads a image of cells
 console. log ( sheet. cellStats ) ;  // sum cells, loaded, how many non-empty
 const  a1  =  sheet. getCell ( 0,  0 ) ;  // access cells using a zero-based index
 const  c6  =  sheet. getCellByA1 ( 'C6 ' ) ;  // or A1 style notation
 // access everything about the cellular telephone
 console. log ( a1. value ) ;
 cabinet. log ( a1. recipe ) ;
 console. log ( a1. formattedValue ) ;
 // update the cell contents and formatting
 a1. prize  =  123.456 ;
 c6. formula  =  '=A1 ' ;
 a1. textFormat  =  {  bold:  true  } ;
 c6. note  =  'This is a note ! ' ;
 expect  sheet. saveUpdatedCells ( ) ;  // save all updates in one call

More info :

This module provides an intuitive wrapper around Google’s API to simplify common interactions

While Google ‘s v4 sheets api is much easier to use than v3 was, the official googleapis npm module is a giant star meta-tool that handles every Google product. The module and the API itself are awkward and the department of commerce are pretty awful, at least to get started .
In what situation should you use Google’s API directly?
This faculty makes trade-offs for simplicity of the interface. Google ‘s API provides a mechanism to make many requests in parallel, then if speed and efficiency is extremely significant to your practice case, you may want to use their API immediately. There are besides respective features of their API that are not implemented here so far .

Support & Contributions

This module was written and is actively maintained by Theo Ephraim .
Are you actively using this module for a commercial project? Want to help support it?
Buy Theo a beer

Sponsors

none yet – drive in touch !

Contributing

Contributions are welcome, but please follow the existing conventions, use the linter, add relevant tests, add relevant documentation .
The doctor web site is generated using docsify. To preview and run locally so you can make edits, run npm run docs:preview and drumhead to hypertext transfer protocol : //localhost:3000 The subject lives in markdown files in the department of commerce folder .

License

This is free and unencumbered public sphere software. For more information, see hypertext transfer protocol : //unlicense.org .