• Twitter
  • Github
  • Youtube
  • Gitter
  • Slideshare
  • LinkedIn

Desktop Liberation

The definitive resource for Google Apps Script and Microsoft Office automation

  • Home
    • About Desktop Liberation
    • Reusing code from this site
    • My Public GAS Library
    • Privacy and Usage Policy
    • Sitemap
  • Blog
    • Access all published posts
  • Downloads
  • APIS
    • Google API
      • Slides
      • Chrome
      • Docs
      • Drive
      • Execution
      • KnowledgeGraph
      • People
      • Sheets
    • Microsoft API
      • OneDrive
    • REST
    • Video Intelligence
    • Vimeo
    • Vision
  • Apps Script & Java Script
    • Apps Script v8
    • Add-ons
    • Javascript
    • From VBA to Google Apps Script
    • Office JavaScript API
    • Snippets
  • Cloud Platform
    • BigQuery
    • Cloud Functions
    • Firebase
    • Kubernetes
    • Google Cloud Platform
    • Google Cloud Storage
  • Databases
    • BigQuery
    • CockroachDB
    • Database abstraction
    • Elastic
    • Firebase
    • GraphQL
  • Office & VBA
    • From VBA to Google Apps Script
    • Optimization
  • Elastic
  • Full Stack
  • GraphQL
  • Kubernetes
  • Node.js
  • Redis- Vuejs
Random blog posts
  • [ October 16, 2019 ] Looping through files in a directory VBA
  • [ July 15, 2019 ] Use a spreadsheet as a database in 5 minutes Microsoft Office & VBA
  • [ June 13, 2019 ] 2019 – a decade in Apps Script Yearly posts
  • [ February 7, 2019 ] 2018 – yet another year in and out of Google Apps Script Yearly posts
  • [ September 22, 2017 ] Apps Script Back end platform – some thoughts on a different approach Apps Script & Java Script
  • [ August 3, 2017 ] Is it still worth creating blogposts ? Yearly posts
  • [ March 3, 2017 ] How about free VM for Chromebook owners? Docker
  • [ December 28, 2016 ] 2016 – Another year in Apps Script Yearly posts
  • [ December 1, 2016 ] App Maker – Build engaging custom apps on G Suite with minimal coding AppMaker
  • [ August 19, 2016 ] Using an Apps Script webapp to distribute assets Apps Script & Java Script
  • [ August 4, 2016 ] Change from project key to scriptid Apps Script & Java Script
  • [ May 13, 2016 ] What I’ve learned from my first play around with BigQuery/Sheets integration BigQuery
  • [ April 13, 2016 ] Targeting training material for Google Apps Scripters Apps Script & Java Script
  • [ March 14, 2016 ] A belated post – 2015 – a year in Apps Script Yearly posts
  • [ November 6, 2015 ] Null should mean something Apps Script & Java Script
  • [ June 25, 2015 ] Running JavaScript directly from Excel makes ‘going Google’ easier Javascript
  • [ March 24, 2015 ] GitHub and VBA integration GitHub
  • [ February 6, 2015 ] Some best practise notes on DbAbstraction usage Database abstraction
  • [ January 21, 2015 ] First shot at Polymer Deprecated
  • [ January 4, 2015 ] A year in the desktop liberation blog Yearly posts
  • [ January 1, 2015 ] A year in apps script and my bucket list Yearly posts
  • [ December 12, 2014 ] Google Apps Script locking and optimum wait times and the golden ratio Apps Script & Java Script
  • [ September 22, 2014 ] How many requests a second can you throw at your Google Apps Script webapp Apps Script & Java Script
  • [ July 24, 2014 ] Using universal analytics to track your Google Apps Script library usage Apps Script & Java Script
  • [ May 29, 2014 ] Parse.com as a substitute for ScriptDB in Google Apps Script Apps Script & Java Script
  • [ May 16, 2014 ] Eurovision song contest voting oddities – interactive visualization Google Visualization
  • [ May 1, 2014 ] Universal analytics – really hotting up Google Analytics
  • [ March 17, 2014 ] I’ve just been recognised as a Google Developer Expert Yearly posts
  • [ February 6, 2014 ] Google Universal Analytics – getting insight into desktop apps too Microsoft Office & VBA
  • [ February 5, 2014 ] More cool stuff you can do with Google Apps ScriptDb Apps Script & Java Script
  • [ January 27, 2014 ] Are the new Google Sheets faster than the old? Sheets specific
  • [ January 8, 2014 ] Google Apps Script Content Service – Cross Domain Puzzle Apps Script & Java Script
  • [ January 1, 2014 ] Get post data from Blogger into a google Spreadsheet with the Google Blogger API Sheets specific
  • [ December 30, 2013 ] Blog posts’ half life – why bother? Analytics
  • [ December 13, 2013 ] Accessing parse.com and scriptDB noSQL databases from Excel Databases
  • [ December 3, 2013 ] Dealing with Unix timestamps in Excel Microsoft Office & VBA
  • [ November 28, 2013 ] Shared Encryption/decryption library for Google Apps Script. Apps Script & Java Script
  • [ November 20, 2013 ] Tracking your Excel Workbooks with Google Analytics Google Analytics
  • [ November 15, 2013 ] Mitigating for changes in REST API rules and formats Microsoft Office & VBA
  • [ October 30, 2013 ] XML and JSON conversion in VBA Json
  • [ September 30, 2013 ] Excel custom functions that can lookup data in Google Apps Script ScriptDB From VBA to Google Apps Script
  • [ September 27, 2013 ] REST access to list management cloud based functions – plus a VBA example Microsoft Office & VBA
  • [ September 19, 2013 ] Validation and lookups using shared scriptDB lists scriptDB
  • [ September 1, 2013 ] Pinyin Tone converter – Google Apps Script custom functions Apps Script & Java Script
  • [ August 9, 2013 ] What does Google Autocomplete show all over the world ? Google Search
  • [ August 6, 2013 ] Google Docs to Excel Asynchronously Docs
  • [ July 9, 2013 ] Using ScriptDB as a noSQL database for non-Google Apps Script clients Databases
  • [ July 3, 2013 ] Concept browser for site navigation with d3 Apps Script & Java Script
  • [ July 1, 2013 ] Sankey diagrams direct from Excel – update Microsoft Office & VBA
  • [ June 26, 2013 ] Exploring blogs and sites by d3 tag visualization and effect on site visits Apps Script & Java Script
  • [ June 18, 2013 ] Google Fusion Big Data and D3 integration: flight data visualization D3
  • [ June 18, 2013 ] jQuery promises and exponential backoff Apps Script & Java Script
  • [ June 5, 2013 ] Playing around with Tableau Public D3
  • [ June 3, 2013 ] Visualization of Sites to Tags with d3.js D3
  • [ May 28, 2013 ] Create biased random test data in VBA Microsoft Office & VBA
  • [ May 21, 2013 ] Digest authentication and Google Apps Script Apps Script & Java Script
  • [ May 14, 2013 ] parse.com and d3.js mashup D3
  • [ May 8, 2013 ] Connections in electoral data – D3 and VBA follow on from oUseful post ScraperWiki
  • [ May 6, 2013 ] Mashing up electoral data – follow on from oUseful post ScraperWiki
  • [ April 30, 2013 ] Finding nearest match and other palettes in other fixed color schemes Colors
  • [ April 24, 2013 ] Parse.com and ScriptDB performance comparison Colors
  • [ April 19, 2013 ] More playing around with color, parse.com, scriptDb and Google Apps Script Apps Script & Java Script
  • [ April 15, 2013 ] Very interesting GAS performance results – run locally using htmlservice Apps Script & Java Script
  • [ April 10, 2013 ] Executing Google Apps Script Code directly in javascript – GAS as a CDN Apps Script & Java Script
  • [ April 3, 2013 ] Google Apps Script “execution time exceeded” woes Apps Script & Java Script
  • [ April 2, 2013 ] Google Apps Script Color Space Conversions. Apps Script & Java Script
  • [ March 30, 2013 ] Automatic documentation from VBA – including function arguments Microsoft Office & VBA
  • [ March 25, 2013 ] Using jSon/javaScript like structures in VBA Json
  • [ March 23, 2013 ] VBA procedures for CIE LabLch and HSL color scheming Colors
  • [ March 14, 2013 ] Playing around with Color on Google Apps Script Apps Script & Java Script
  • [ March 13, 2013 ] Example of automatic code updating for VBA VBA
  • [ March 12, 2013 ] VBA string manipulation optimization with custom classes Microsoft Office & VBA
  • [ March 8, 2013 ] Inherit link colors from cells – D3 sankey from Excel Microsoft Office & VBA
  • [ March 6, 2013 ] Strings and the garbage collector in VBA : optimizing string concatenation VBA
  • [ February 28, 2013 ] API of the Day: facebook fql direct from Excel and Google APPs API Specific
  • [ February 23, 2013 ] Adventures in serializing VBA classes – automatically documenting your modules Microsoft Office & VBA
  • [ February 20, 2013 ] Unused tube stations: discovered via scraperwiki- mapped with Excel and Google Maps Integrating Excel with Maps and Earth
  • [ February 18, 2013 ] Scraperwiki horsemeat data to Sankey, via Excel. Microsoft Office & VBA
  • [ January 21, 2013 ] ‘Hot doughnut’ chart in Excel Colors
  • [ January 11, 2013 ] Can you make any adSense money with a blog ? adsense
  • [ January 10, 2013 ] Calculating a friendly font color based on the fill color – Google Apps Script Colors
  • [ January 7, 2013 ] I’m awarding badges – Google Apps Script integration with Persona and Mozilla Openbadges Badges
  • [ December 20, 2012 ] Playing around with awarding badges on sites Badges
  • [ December 9, 2012 ] Getting physical again No category
  • [ December 8, 2012 ] Selecting and publishing a function from a web source Apps Script & Java Script
  • [ December 7, 2012 ] Publish and prettify source directly from a url Apps Script & Java Script
  • [ December 2, 2012 ] Zoomable d3.js partition charts straight from Excel D3
  • [ November 22, 2012 ] Select, prettify publish snippets in multiple languages from within a GIST module Apps Script & Java Script
  • [ November 22, 2012 ] Selecting specific Gist and GAS functions for publication Apps Script & Java Script
  • [ November 20, 2012 ] Publishing and prettifying Gists through google apps script Apps Script & Java Script
  • [ November 17, 2012 ] Using Google Cache to persist data across function calls Apps Script & Java Script
  • [ November 12, 2012 ] Google Apps Script Liberation – publishing your stuff Apps Script & Java Script
  • [ November 6, 2012 ] Publishing Google Apps Script code snippets Apps Script & Java Script
  • [ November 5, 2012 ] Google Apps Script – a new day – a new feature Apps Script & Java Script
  • [ November 2, 2012 ] Delegating processing to the cloud from Excel and other platforms Apps Script & Java Script
  • [ November 1, 2012 ] Keeping all those Google Apps script ‘shortcuts’ under control Apps Script & Java Script
  • [ October 31, 2012 ] Somewhere to keep those API keys: google apps script rest library entry Apps Script & Java Script
  • [ October 31, 2012 ] API library entry of the day: use builtwith.com to see how websites are built Apps Script & Java Script
  • [ October 30, 2012 ] Shortening a string, but keeping it unique: Google Apps Script Caching Keys Apps Script & Java Script
  • [ October 29, 2012 ] Google Caching and faking jsonp Apps Script & Java Script
  • Twitter
  • Github
  • Youtube
  • Gitter
  • Slideshare
  • LinkedIn
HomeGoogle Cloud PlatformService account impersonation for Google APIS with Nodejs client

Service account impersonation for Google APIS with Nodejs client

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.
Page Content hide
1 Preparation
2 Node set up
3 Accessing the sheets API
4 The created spreadsheet
5 The Sheets API
6 Create a spreadsheet
7 Making a range
8 Next
9 More

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
More Google Cloud Platform topics below
  • Chunking promises using the Knowledge Graph API as an example
  • Cloud Storage and Apps Script
    • Enabling APIs and OAuth2
    • GcsStore examples
    • GcsStore overview - Google Cloud Storage and Apps Script
    • Google cloud storage and CORS
    • Setting up or creating a console project
    • Using the service account to enable access to cloud storage
  • Connecting to cockroachdb
  • Firebase auth for graphql clients
  • FTP server on Kubernetes with cloud storage and pubsub
  • Getting an API running in Kubernetes
    • Bringing up an ingress controller
    • Building your App ready for Kubernetes deployment
    • Creating a Kubernetes deployment
    • Creating a microservice on Kubernetes
    • Digging around on the Kubernetes cluster
    • Getting an ssl certificate for Kubernetes ingress
    • HTTPS ingress for Kubernetes service
    • Kubernetes ingress with cert-manager
    • Managing ssl for ingress certificates with cert-manager
  • Getting cockroachdb running on google cloud platform
  • Getting cockroachDB running with Kubernetes
  • Getting memcache up and running on Kubernetes
    • Creating a test app for memcache on Kubernetes
    • Exposing a memcache loadbalancer
    • Getting a simple app running on Kubernetes
    • Installing memcache with Kubernetes
    • Using mcrouter with memcached on Kubernetes
  • Google Cloud Run on Kubernetes
  • Google Video Intelligence API film labelling
  • Google Vision and OCR
  • Long running cloud platform operations and node apis
  • Making sense of OCR - Google Vision
    • Orchestrating APIS to structure and interpret OCR data
  • More cloud streaming
  • Orchestrating APIS to analyze OCR data
  • Queuing asynchronous tasks to defeat rate limits
  • Secure CockroachDB and Kubernetes
  • Securing Graphql with firebase login
  • Stream content to Google Cloud Storage
  • Video transcription with Video Intelligence API
  • Your own free linux VM

 

Select a Category

My Books and Videos


All formats are available from O'Reilly, Amazon and all good bookshops. You can also read a preview on O'Reilly

A video course over about 8 hours and 70 lessons taking you through the basics of Apps Script and JavaScript. Available at O'Reilly, Amazon, Infinite Skills & all good media outlets

Google Apps Script for Beginners: A video course over about 8 hours and 70 lessons taking you through the basics of Apps Script and JavaScript. Available from O'Reilly, Infinite Skills and all good media outlets

bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines

Tag Cloud

Your browser doesn't support the HTML5 CANVAS tag.

  • Kubernetes
  • vba
  • snippets
  • maps
  • parse
  • Optimization
  • Google Cloud
  • Scriptdb
  • apps script
  • ephemeral exchange
  • JavaScript
  • D3
  • Json
  • earth
  • Deprecated
  • Visualization
  • colors
  • Oauth2
  • Apps script snippets
  • excel
Book – Also available on Amazon
Video – Also available on Amazon
Video-For beginners
  • Twitter
  • Github
  • Youtube
  • Gitter
  • Slideshare
  • LinkedIn

bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines