It’s time for the important tech content that is part of being a serious software professional with a blog. It’s an amazing coincidence that recruiters are reaching out to me about jobs using React and Node.js – the exact same stack that is used by the team that I am on!

Since I am not a recipe blogger, I am not going to hide the important stuff at the end, after my personal story. If you are using a service account and not OAuth2 to authenticate to the Google Sheets API, the standard advice is to put the location of the credential into an environment variable. Which is ok, I guess, for just fussing around on your laptop but is never going to fly in production. I need the credential to be in a config file.

const getAuthToken = async function() {
  const auth = new google.auth.GoogleAuth({
    keyFile: "/path/to/credential.json",
    scopes: ["https://www.googleapis.com/auth/spreadsheets"],
  });
  const authToken = await auth.getClient();
  return authToken;
}

This has been working for me. I think I found it suggested in one of the READMEs in the GitHub repo for the npm library for the Google APIs. I hunted a very long time before I found it, and based on what I saw in the various dead ends that I encountered along the way, I am not the first person to have been in this situation.

Back to our meandering and pointless narrative.

As has been a theme at work lately, people want decisive business information reported to them in spreadsheets. Not to brag, but the number of KPIs that we have has been taking off like a rocketship!!! Thus, I spent the past few days making a reporting tool in Node and React where you enter some information on a page and click a button and BOOM! the data you want magically appears in a Google Sheet.

Do you want to use Node to make your own Google Sheets reporting tool? The first thing that you need to do is to read this blog post by Mandeep Singh Gulati who figured it out and did a really good job of writing it up. I found this to be about a thousand times more useful than Google’s documentation of the Sheets API. Admitedly, I’ve never been particularly impressed by Google’s documentation of their APIs, but the Sheets API is really well documented! But the blog post that I linked to is still way, way better. Read that blog post and refer to the documentation and you will also be well on your way to having a fullstack JavaScript reverse ETL pipeline (or whatever).

The other thing that I missed upon my first reading of the documentation is that the request body is an object named resource. So if you are writing [["hi", "from"], ["my", "spreadsheet"]] to some cells of your spreadsheet, you would do something like

// Assume that you already have the spreadsheetId and auth
const request = {
  spreadsheetId,
  auth,
  valueInputOption: USER_ENTERED,
  range: "B1:C2",
  resource: {[["hi", "from"], ["my", "spreadsheet"]]}  
};

const response = await sheets.spreadsheets.values.update(request);

When it comes to the React side of things, you are on your own. Since React is made out of magic and lets you do silly things, I did silly things. For example, when you click the button that sets off this entire process and runs a bunch of database queries before sending the results to Google Sheets, I take away the button. That is right, the mere act of clicking the button makes it go away until the data has landed in the sheet. You can not keep clicking and clicking and clicking while you wait because the button is not there anymore. This is why React is a valuable part of your analytics pipeline.

Speaking of KPIs, the mere act of creating an empty page with a button in React took about 100 lines of boilerplate spread out across six files – before the button even did anything!