Working with the Google Sheets API in Nextjs

With the API, developers can read and write data in Google Sheets, create new spreadsheets and worksheets, format fonts and cell backgrounds, add images and charts and much more. The Google Sheets API includes authentication protocols and libraries for JavaScript, making it easy for developers to set up and use the service in their application. Developers can also use the API to automate tasks, such as creating recurring tasks and tracking user activity.

Use Case

Therefore it is a really good use case when making small Websites that maybe require a little bit of dynamic content. As pretty much everyone with a computer is familiar with Google Sheets, it makes it a very good contender to use on a website where you want a non-technical person to be able to update specific content on occasion.

Advantages

- Free (pretty much for small amounts of data)

- Easy to implement

- Easy to update for non technical people

Google Cloud Console

So before using the Google Sheets API, you have to go to the Google Cloud console and enable the Google Sheets API. It will also require you to create credentials. In this example I have created a Service Account but if you see the Google documentation it can also be done via OAuth 2.0 Client IDs. Once you have created the credentials and enabled the API then you can start the implementation!

How to use it

I won't go into the ins and outs of Nextjs on this blog post but rather just explain the specifics when connecting to the Google Sheets API. If you don't understand something specific to Nextjs then check out their documentation. It is really good!

Connecting to the Google Sheets API

In order to retrieve any data on a google sheet, you have to do it through the Google Sheets API. Google have their own documentation for the API but I tend to find their docs are OK but there are always a few gaps that need filling in if you want to implement a fully working example.

On this particular example I am using `v4` of the google sheets api and I created an asynchronous function to retrieve the data in a `services` folder. I am using the googleapis npm package. It's a pretty big package in terms of size (345.4k gzipped) but for this example, I won't worry about that.

import { google } from "googleapis";

export async function getGoogleSheetData() {
  try {
    const scopes = ["https://www.googleapis.com/auth/spreadsheets.readonly"];
    const jwt = new google.auth.JWT(
      process.env.GOOGLE_SHEETS_CLIENT_EMAIL,
      undefined,
      // we need to replace the escaped newline characters
      // https://stackoverflow.com/questions/50299329/node-js-firebase-service-account-private-key-wont-parse
      process.env.GOOGLE_SHEETS_PRIVATE_KEY!.replace(/\\n/g, "\n"),
      scopes
    );

    const sheets = google.sheets({ version: "v4", auth: jwt });
    const response = await sheets.spreadsheets.values.get({
      spreadsheetId: process.env.SPREADSHEET_ID,
      range: "example_range",
    });

    return handleSpreadSheetApiResponse(response.data.values);
  } catch (err) {
    console.log(err);
  }

  return [];
}

This `getGoogleSheetData` function will be called in `getServerSideProps` on the page I wish to fetch the data for. It is an asynchronous function and the call to the Google Sheets API is wrapped in a `try catch` block in case there is some error fetching the data.

This is pretty much taken from the documentation. One important part to bear in mind is the `auth: jwt`. As you can see it required a bit of trickery to get the `GOOGLE_SHEETS_PRIVATE_KEY` in the right format. Once you have this token for the auth it's a matter of using the `sheets` function from the `googleapis` library and then specifying the `spreadsheetId` and the `range` (sheet name) from which you want to fetch the data.

Once you receive the data back you will most likely have to format it. I am doing this in a `handleSpreadSheetApiResponse` function. This will all vary depending on the structure of the data you are fetching in the first place. Best thing to do is to `console.log` the response and then determine how you wish to format the data.

For some context, this is the `handleSpreadSheetApiResponse` function:

function handleSpreadSheetApiResponse(googleSheetRows: any[]) {
  if (googleSheetRows) {
    return googleSheetRows.slice(1).reduce((obj: any, item) => {
      return Object.assign(obj, { [item[0]]: item[1] });
    }, {});
  }
  return;
}

Using the Google Sheets API Service Function

In order to use this function that has now been set up it can be called in `getServerSideProps`

export async function getServerSideProps() {
  const soups = await getGoogleSheetData();

  return {
    props: {
      soups,
    },
  };
}

Very straightforward really. The function is called and this fetches the data. Once you return the (formatted) data, its a matter of passing it down as props to the page and then it can be used as you wish!

Conclusion

Connecting to the Google Sheets API is similar to connecting to any other API, the trickiest part was getting the right authentication but thankfully Stack Overflow came to my rescue on this occasion.

Google Sheets API is very useful for small sites that need a little bit of dynamic data that needs updating occasionally. I would not use this for a blog or anything that needs regular new data or regular updates to large pieces of data. There are better solutions out there for that. However, Google Sheets does provide a nice, cheap, easy to implement solution.