If you want to write to sheets from a server side Node app, you can use a service account, but it’s a little tricky as you have to authorize that service account to be able to access the spreadsheets of a given user. One way is for the spreadsheet owner to share the spreadsheet with the email address of the service account, but if you need to be able to create sheets, and not bother with that you can allow the service account to impersonate a given user.
There are a couple of gotchas to look out for, and the documentation for the API and authorization favors python, and/or use the normal 3 legged oauth2 approach which is not practical for many automated operations – so here’s the full Nodejs story.
Preparation
Most of this you’ll know how to do, or you’ll find elsewhere on this site, so I won’t burden you with lots of screenshots, but here’s what you have to do
- create a service account, and download the file
- allow that service account to do this – if you won’t or can’t, then stop here because it’s required for this method.
- get the client id of the service account and in admin.google.com security/advanced settings/manage API client access, you’ll find an ancient-looking console. Enter the numeric service account clientId (you’ll be tempted to add .googleusercontent.com like the other stuff there, but don’t – it won’t work) and then give the scopes you want to allow it to have. In my case, it’s just about spreadsheets. Don’t forget, you are now allowing this service account to impersonate anyone on the domain – so think about it carefully.
- enable the API you plan to use in the project belonging to the service account (this example is for the sheets API, but will work with most other APIS too)
Node set up
Install the googleapis module with yarn or npm and include it in your app
|
1 |
const {google} = require('googleapis'); |
Set up your secrets however you usually manage them in node. I generally have a secrets file where all parameters are stored so I’ll be referencing them here. You’ll have to create these methods to pick up the secrets from however you store them. Here are the secrets I’ll be needing during this.
- getSheetCreds is the contents of my service account JSON file
- getSheetSubject is the email address of the person I want to impersonate
- getSheetScopes is the scopes I want to be available (this should match or be a subset of those you set up back in the admin console
|
1 2 3 4 5 |
const { getSheetCreds, getSheetSubject, getSheetScopes } = require('../private/visecrets'); |
Create an init function to get the authentication going. You’ll see reference to ‘mode’ here. That’s just something I use to be able to use different credentials for different modes from my secrets file, so you can ignore it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
const init = async ({mode}) => { const {credentials} = getSheetCreds({mode}); // jwt includes account to impersonate const auth = new google.auth.JWT( credentials.client_email, null, credentials.private_key, getSheetScopes(), getSheetSubject({mode}) ); // validate and authorize the jwt const {result, error} = await till(auth.authorize()); if (!error) { console.log( `service account ready to access sheets on behalf of ${getSheetSubject({ mode, })}` ); return google.sheets({ version: 'v4', auth, }); } else { console.error(err); } }; |
if you copy that exactly you’ll need this little function which does async error handling
|
1 2 |
const till = waitingFor => waitingFor.then(result => ({result})).catch(error => ({error})); |
The init function will return a sheetsClient with all the authentication details built-in, so after this point, you don’t need to worry about any tokens or anything else.
Accessing the sheets API
This article is about the authentication/impersonation bit – and that’s all done now, but let’s create a spreadsheet, a sheet, and put some data in it. We’ll get to the functions that do the work shortly.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
(async () => { // initialize auth const sheetClient = await init({mode}); // make up some data const values = [ ['Name', 'Profession', 'Address'], ['Sean Connery', 'Actor', 'Bahamas'], ['Richard Branson', 'Businessman', 'Necker Island'], ['Celine Dion', 'Singer', 'Henderson, Nevada'], ]; // create a spreadsheet, add a sheet, insert some values createSpreadsheet({title: 'my spreadsheet', sheetClient}) .then(spreadsheet => createSheet({ title: 'first sheet', spreadsheet: spreadsheet.data, sheetClient, }).then(sheet => addValues({ spreadsheet: spreadsheet.data, sheet: sheet.data.replies[0].addSheet.properties, sheetClient, values, }) ) ) .catch(console.error); })(); |
The created spreadsheet
Unsurprisingly, here’s the result.
The Sheets API
The data structures are a bit fiddly, so that’s why I like to separate them out. The approach is the same for them all, namely, build the request, then fire it off. The important part if that the sheetClient knows whose account to build it in and automatically generates all the necessary token info to make it happen.
Create a spreadsheet
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
const createSpreadsheet = async ({title, sheetClient}) => { const request = { fields: 'spreadsheetId', resource: { properties: { title, }, }, }; console.log('...creating new spreadsheet ', title); return sheetClient.spreadsheets.create(request); }; |
Create a sheet
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
const createSheet = ({title, spreadsheet, sheetClient}) => { const request = { spreadsheetId: spreadsheet.spreadsheetId, resource: { requests: [ { addSheet: { properties: { title, }, }, }, ], }, }; console.log('...creating new sheet ', title); return sheetClient.spreadsheets.batchUpdate(request); }; |
Add some values
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
const addValues = ({ spreadsheet, sheet, sheetClient, values, rowOffset, columnOffset, }) => { const {title, sheetId} = sheet; const range = `'${title}'!${rangeMaker({ rows: values.length, columns: values[0].length, rowOffset, columnOffset, })}`; const request = { spreadsheetId: spreadsheet.spreadsheetId, resource: { valueInputOption: 'USER_ENTERED', data: [ { range, values, }, ], }, }; console.log('...adding values to ', range); return sheetClient.spreadsheets.values.batchUpdate(request); }; |
Making a range
One of the annoying things about this API is that you need to generate an A1 notation range to accept the data, so you’ll notice I’m using a function to do that. Here it is
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/** * create a column label for sheet address, starting at 1 = A, 27 = AA etc.. * @param {number} columnNumber the column number * @return {string} the address label */ const columnLabelMaker = (columnNumber, s) => { s = String.fromCharCode(((columnNumber - 1) % 26) + 'A'.charCodeAt(0)) + (s || ''); return columnNumber > 26 ? columnLabelMaker(Math.floor((columnNumber - 1) / 26), s) : s; }; const rangeMaker = ({rows, columns, rowOffset, columnOffset}) => `${columnLabelMaker((columnOffset || 0) + 1)}${(rowOffset || 0) + 1}:${columnLabelMaker((columnOffset || 0) + (columns || 1))}${(rowOffset || 0) + (rows || 1)}`; |
Next
This example is an actual part of a much larger workflow which makes use of a range of APIS. If you found this useful, you may also like some of the pages here.
More
Since G+ is closed, you can now star and follow post announcements and discussions on Github, here