Reading Cells From Google Sheets in Javascript
google-spreadsheet
The almost popular Google Sheets API wrapper for javascript
- multiple auth options - service account (due west/ optional impersonation), OAuth 2.0, API key (read-only)
- jail cell-based API - read, write, bulk-updates, formatting
- row-based API - read, update, delete (based on the quondam v3 row-based calls)
- managing worksheets - add together, remove, resize, change title, formatting
Docs site - Total docs available at https://theoephraim.github.io/node-google-spreadsheet
🚨 Google Deprecation Warning - affects older version (v2) of this module🚨 Google is phasing out their old v3 api, which the older version of this module used. Originally they were going to shut information technology down on March 3rd 2020, but accept 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 --saveoryarn add google-spreadsheet
Examples
the following examples are meant to give you an idea of but some of the things yous can do
Important NOTE - To keep the examples concise, I'm calling await at the top level which is not immune past default in most versions of node. If you need to call look in a script at the root level, you must instead wrap it in an async function similar and so:
( async function ( ) { await someAsyncFunction ( ) ; } ( ) ) ; The Basics
const { GoogleSpreadsheet } = require ( 'google-spreadsheet' ) ; // Initialize the sail - doc ID is the long id in the sheets URL const medico = new GoogleSpreadsheet ( '<the sheet ID from the url>' ) ; // Initialize Auth - run across https://theoephraim.github.io/node-google-spreadsheet/#/getting-started/hallmark await doc . useServiceAccountAuth ( { // env var values are copied from service business relationship credentials generated past google // run across "Authentication" section in docs for more info client_email: process . env . GOOGLE_SERVICE_ACCOUNT_EMAIL , private_key: process . env . GOOGLE_PRIVATE_KEY , } ) ; expect physician . loadInfo ( ) ; // loads document properties and worksheets console . log ( doc . championship ) ; await doc . updateProperties ( { title: 'renamed doc' } ) ; const sheet = doc . sheetsByIndex [ 0 ] ; // or utilise doc.sheetsById[id] or doc.sheetsByTitle[title] console . log ( sheet . title ) ; console . log ( sheet . rowCount ) ; // calculation / removing sheets const newSheet = await doc . addSheet ( { championship: 'hot new sheet!' } ) ; expect newSheet . delete ( ) ; More info:
- GoogleSpreadsheet
- GoogleSpreadsheetWorksheet
- Authentication
Working with rows
// create a sail and set the header row const canvas = await physician . addSheet ( { headerValues: [ 'proper name' , 'email' ] } ) ; // append rows const larryRow = wait sheet . addRow ( { name: 'Larry Page' , email: 'larry@google.com' } ) ; const moreRows = wait sail . addRows ( [ { name: 'Sergey Brin' , e-mail: 'sergey@google.com' } , { name: 'Eric Schmidt' , email: 'eric@google.com' } , ] ) ; // read rows const rows = await canvass . getRows ( ) ; // can pass in { limit, offset } // read/write row values console . log ( rows [ 0 ] . name ) ; // 'Larry Page' rows [ 1 ] . email = 'sergey@abc.xyz' ; // update a value expect rows [ 1 ] . save ( ) ; // save updates await rows [ i ] . delete ( ) ; // delete a row More info:
- GoogleSpreadsheetWorksheet > Working With Rows
- GoogleSpreadsheetRow
Working with cells
expect sheet . loadCells ( 'A1:E10' ) ; // loads a range of cells console . log ( sheet . cellStats ) ; // total cells, loaded, how many non-empty const a1 = sheet . getCell ( 0 , 0 ) ; // access cells using a zero-based alphabetize const c6 = sheet . getCellByA1 ( 'C6' ) ; // or A1 style notation // access everything almost the cell panel . log ( a1 . value ) ; console . log ( a1 . formula ) ; panel . log ( a1 . formattedValue ) ; // update the cell contents and formatting a1 . value = 123.456 ; c6 . formula = '=A1' ; a1 . textFormat = { bold: true } ; c6 . note = 'This is a annotation!' ; expect canvass . saveUpdatedCells ( ) ; // salve all updates in ane call More than info:
- GoogleSpreadsheetWorksheet > Working With Cells
- GoogleSpreadsheetCell
Why?
This module provides an intuitive wrapper around Google's API to simplify common interactions
While Google's v4 sheets api is much easier to utilise than v3 was, the official googleapis npm module is a giant meta-tool that handles every Google product. The module and the API itself are awkward and the docs are pretty terrible, at least to get started.
In what situation should you lot use Google's API directly?
This module makes trade-offs for simplicity of the interface. Google'due south API provides a machinery to make many requests in parallel, and then if speed and efficiency is extremely important to your employ example, y'all may want to use their API directly. There are besides several features of their API that are not implemented here yet.
Back up & Contributions
This module was written and is actively maintained past Theo Ephraim.
Are you actively using this module for a commercial project? Want to help support it?
Buy Theo a beer
Sponsors
None yet - get in affect!
Contributing
Contributions are welcome, but please follow the existing conventions, utilise the linter, add relevant tests, add relevant documentation.
The docs site is generated using docsify. To preview and run locally so you tin can make edits, run npm run docs:preview and caput to http://localhost:3000 The content lives in markdown files in the docs folder.
License
This is free and unencumbered public domain software. For more info, see https://unlicense.org.
rutledgeappre1994.blogspot.com
Source: https://www.npmjs.com/package/google-spreadsheet
Belum ada Komentar untuk "Reading Cells From Google Sheets in Javascript"
Posting Komentar