(2 minute read)
For some recent client work I needed to record the results of running performance tests in a conveniently accessible place for the rest of the team, i.e. a Google spreadsheet.
The tests were being run in CI, meaning that we had to be able to update the spreadsheets from the CI server itself using the programmatic API.
Our spreadsheet was private (i.e. only visible within our organisation). But the Git repository running our performance tests is also private, meaning we'd be able to store some non-public data (e.g. access tokens) within it.
We have 3 scripts which work together to enable us to write to our spreadsheet:
Follow the quick start instructions to enable the Sheets API for your account.
You will receive a credentials JSON file that looks like:
{
"installed": {
"client_id": "239872323-alsdjflaskdjf9823sld.apps.googleusercontent.com",
"project_id": "ecom-performance-testing",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://accounts.google.com/o/oauth2/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_secret": "jsdf89723ihsdoisd_",
"redirect_uris": ["urn:ietf:wg:oauth:2.0:oob", "http://localhost"]
}
}
Save this file to your Git repo. We save it in .credentials/client_secret.json.
The Sheets v4 API requires an OAuth-authenticated access token to be used if one needs to write to a spreadsheet.
But since we're running in a CI environment that's non-interactive that's not going to be possible to do at runtime. So our script will work as follows:
Here is the script:
import fs from 'fs';
import path from 'path';
import promisify from 'es6-promisify';
import readline from 'readline';
import GoogleAuth from 'google-auth-library';
const credentialsPath = path.join(__dirname, '.credentials');
const credentialsFile = path.join(credentialsPath, 'client_secret.json');
const tokenFile = path.join(credentialsPath, 'token.json');
export const getClient = async () => {
const credentials = require(credentialsPath);
const {
installed: {
client_secret: clientSecret,
client_id: clientId,
redirect_uris: redirectUrls
}
} = credentials;
const redirectUrl = redirectUrls[0];
const auth = new GoogleAuth();
const client = new auth.OAuth2(clientId, clientSecret, redirectUrl);
try {
client.credentials = require(tokenFile);
} catch (err) {
console.warn(`Error loading token: ${err}`);
client.credentials = getNewToken(client);
}
return client;
}
const getNewToken = async client => {
// if not running in CI then tell user to run the script locally
if (process.env.CI) {
throw new Error('The OAuth token has expired. Please run this script locally to obtain a new token.');
}
// generate auth URL
const authUrl = client.generateAuthUrl({
access_type: 'offline',
scope: ['https://www.googleapis.com/auth/spreadsheets']
});
// tell user to visit the URL
console.log(`\nPlease visit the following URL to obtain an authorisation code:`, authUrl);
// let user input the obtained code
const rl = readline.createInterface({
input: process.stdin,
output: process.stdout
});
const code = await new Promise(resolve => {
rl.question(`\nEnter the code from that page here: `, resolve);
});
rl.close();
// get token corresponding to the code from Google
const token = JSON.stringify(await promisify(client.getToken, client)(code), null, 2);
console.log('New token:', token);
// save token
fs.writeFileSync(tokenFile, token);
console.log(`\nNew auth token has been saved to disk.`);
return token;
}
Note that we're saving the token in plaintext in .credentials/token.json. If this was a public Git repo and we wanted to keep the token a secret we could encrypt and then decrypt it on the fly during the CI build. The decryption key could be provided via an environment variable on the CI build server.
Now that we have api.js in place, this script is straightforward:
import promisify from 'es6-promisify';
import Google from 'googleapis';
import { getClient } from './api';
const main = async () => {
// get the authenticated client
const client = await(getClient());
// init the API
const sheets = Google.sheets('v4');
const asyncGet = promisify(sheets.spreadsheets.values.get, sheets.spreadsheets.values);
// get spreadsheet data from cells: Sheet1, A1:B1
const res = await asyncGet({
auth: client,
spreadsheetId: '23jsdfljslfkjsdkfjsldfjsdf',
range: 'Sheet1!A1:B'
});
console.log(res);
console.log('Spreadsheet accessed successfully!')
}
main().catch(err => {
console.error(err);
throw err;
});
The ping script simply checks that we can actually access the spreadsheet. This checks that our API token is valid and that the Sheets API is working as expected.
Note that we use A1 Notation to actually refer to the cells we wish to check.
The update.js script is also straightforward to write:
import fs from 'fs';
import path from 'path';
import promisify from 'es6-promisify';
import Google from 'googleapis';
import { getClient } from './api';
const main = async () => {
// get auth client
const client = await(getClient());
// init api
const sheets = Google.sheets('v4');
const asyncAppend = promisify(sheets.spreadsheets.values.append, sheets.spreadsheets.values);
// append data
await asyncAppend({
auth: client,
spreadsheetId: 'sflasdhflsh293lasdlsdf',
range: `Sheet1!A:E`,
valueInputOption: 'RAW',
insertDataOption: 'INSERT_ROWS',
resource: {
range: `${name}!A:E`,
majorDimension: "ROWS",
values: [
[
'2018-01-01',
23.4,
1.2,
9.5,
"looks good"
]
]
}
})
}
main().catch(err => {
console.error(err);
throw err;
});
You can customize and paramterize the above scripts if you need something more generic you want to use for any spreadsheet.