New App, who dis?

Next.js new App router changes many aspects of Next projects beyond folder structure. So does that impact interacting with Google APIs?

Look at all that code!

Next.js is an exceptionally popular framework for JavaScript. It's not hard to see why it has gained so much traction, as it takes the powerful react library, and removes the single page application limitation, whilst also providing server side rendering in addition to a slew of exciting features.

Next.js new folder structure

Pre version 13, Next.js projects are structured with a page folder containing javascript files corresponding to individual pages. This structure works really well, but has a few issues of scaling. As projects grow in size and complexity, the separation of the page from page specific components can become harder to differentiate.

Next.js pages folder structure example, pages are located in a separate folder with page components in their own folder.

The new apps project directory is set up with an apps folder, in the apps folder, other folders represent the subpage, for example app/project would direct to http://{website}/project. The content of the page is stored in a page.jsx (or tsx) file.

One advantage of this setup is that React apps can be dropped nearly wholesale into a Next.js app, by changing the App.jsx file to page.jsx, allowing for easier modulation of Next.js projects.

Next.js app folder structure

Next.js and get()

Another major change is the streamlining of data fetching on server side rendering. Pages based Next.js uses a getServerSideProps() function, but this is deprecated in App based Next.js folder structure. Instead, the generic get() request is used in API calls, and a new use() function is imported from React. use() wraps around the get() request to produce asynchronous data fetching in conventional React styled functions. This streamlines the whole coding experience into a more conventional structure, page folders are structured like React apps and getServerSideProps() is simplified to fetch requests. But what does that look like in code?

Next.js and accessing Google Applications

Google is a ubiquitous presence in the world of web development, with Google Cloud Services hosting multiple applications with strong API support. For web developers, access to the docs or sheets function can serve as a simple and effective method for data storage and retrieval. So how do you access data from Google? Below are 3 methods for accessing Google apps from a Next.js server.

Initial data structure

Before we start with fetching data from Google, we first need data on Google to fetch. A quick creation of a new sheet named GoogleFetch examples and a small amount of data for extraction below.

While this example is setup for Google sheets, it also works with Google Docs, and can be expanded to work with Google Drive allowing for multiple documents of different types to be gathered.

A Google sheet, setup with a simple data set

You can find the sheet here. Note that for some of the fetch requests, the sheet will need to be readable by anyone.

Direct fetch from website with Google Visualisation Query

The first method is a direct access of the webpage that the Google app is hosted on, this works with Sheets, Docs, Slides, but best with Drive and files that are directly fetched, as the display of sheets/docs is automatically parsed, and the outcome can be somewhat unexpected.

Below is an example of directly fetching the data from Google, note the file will need to be accessed by all as there is no identification involved.

Generating the link

In order to generate a link for the fetch request we first need to get the link from

Remove the ‘edit#’ and replace it with gviz/tq?sheet= and the name of your sheet, in this case the sheet is called page1. gviz is short for Google Visualisation Query and will return a string.

The fetch request is simply structured like this:

// Raw fetch, returns column list, all data is returned
async function fetchSheetsDirect() {

  const response = await fetch( 'https://docs.google.com/spreadsheets/d/1xAY6jBd5cJPIF3UfnsDveUZL-f_qNShGb3WFQItq-vw/gviz/tq?sheet=sheet1');

  const data = await response.text();
  const returnValue = data;
  return {
    returnValue,
  };
}

The return value that’s produced is a text file that as raw is passed as:

Looking at the data you can see that a JSON is present after the google.visualization.Query.setResponse( part of the text file, by using the substring() function to start after this section, and the slice() to remove the corresponding ); off the end of the string, we can get a JSON that can be parsed.

// Raw fetch, returns column list, all data is returned
async function fetchSheetsDirect() {
  const response = await fetch(
    "https://docs.google.com/spreadsheets/d/1xAY6jBd5cJPIF3UfnsDveUZL-f_qNShGb3WFQItq-vw/gviz/tq?sheet=sheet1"
  );

  const data = await response.text();
  const dataParsed = JSON.parse(data.substring(47).slice(0, -2));
  const returnValue = dataParsed;
  return returnValue;
}

The full fetch function for direct sheet fetching

Part of the JSON is a table with two objects, cols and rows. The cols object just gives the values of the header of the sheet, e.g. A B C, but the rows provide the data in the sheet, in an object with the key and an array of objects themselves with the key v for value for all the rows with data to the last filled row. Empty cells return a null response so as to maintain the structure.

Therefore by mapping the data, it is possible to extract the required data with a few lines of code, below is setup using the react use hook to fetch the data, a useEffect could also be employed to get the data on page load.

  const directFetchData = [];
  const directFetchSheetData = use(fetchSheetsDirect());
  const rowData = directFetchSheetData.table.rows.slice(1);
  for (const row in rowData) {
    let newArray = rowData[row];
    let pushArray = [];
    newArray.c.map((value) => {
      pushArray.push(value.v);
    });
    directFetchData.push(pushArray);
  }

This code will create an array that can be mapped into

Pros of using direct fetch with Google Visualisation Query

The major advantage of using direct fetch is the minimum amount of code required to set up the function. With only the two code snippets above, you can get an array that maps into a React component easily. If you only need lightweight data and you’re not worried about writing to the Google sheet, nor others viewing the sheet directly, then this represents a quick and efficient way of storing and fetching your data.

Cons of using direct fetch with Google Visualisation Query

While a very simple system to set up, it’s also the most limited in terms of scope. Firstly the whole sheet, not just the page in question must be available to everyone, as there is no authorisation used. This poses some potential privacy issues. Additionally this method will only work with a single sheet/doc, while other methods can access the Drive and pull multiple files.

Additionally, there is an issue with the data presentation, the whole sheet is provided as an array of objects, which in turn can make data structuring more complex.

Finally there’s no Post function, meaning that the sheet can be used as a data reference, but can’t be updated outside of direct Sheet manipulation.

Google Apps Script web link

The Google Workspace provides a very robust scripting platform for lightweight application development known as the Google Apps Script. The key features of this platform is that it contains several Google Apps specific tailored functions written in Node.js. What this breaks down to, is a simplistic system for frontend developers with javascript knowledge to quickly create apps that allow websites to interact with Google Workspace.

For us in React/NextJS we have access to a system that can generate JSONs of our Google sheet for RESTful interaction.

Getting started

Starting with Google Apps Script is easy, first you need to go to your Google sheet, and then click extensions and in the dropdown menu select Apps Script to be taken to the Apps Script interface.

Apps Scripts is very robust and has many different potential functions, far too many for an individual blog post. So here I will stick to the basics of making a script specific to this sheet.

Setting up a Google Apps Script file

From getting and titling the Apps Script file, the first thing to do is to Deploy a web application. This will require approval, and once this is done, the code will run, and a weblink will be provided.

In the Apps Script, getting the data can be done in a single line via a doGet() refunction, which handles get fetch requests, but I split it into two, one to get the sheet, and one to get the values to make the code more

After that it’s as simple as stringifying the data and then using the ContentService to display the data as a text output.

function doGet() {
  // get values from the active sheet
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();

  // Convert data into a JSON file
  const val = JSON.stringify(data);

  // Create a webpage with a JSON response
  return ContentService.createTextOutput(val).setMimeType(ContentService.MimeType.JSON)
}

The Google Apps Script provides simple data update

After this code is finished, the script is redeployed and a web app. The JSON is pre-parsed so the fetch request will get an array that can be mapped just like the direct fetch call.

Going to the web link via a browser reveals an array of arrays with the data, ready to be parsed into a program

// Basic fetch via Apps Script html URL returns parsed JSON
async function fetchAppsScript() {
  const response = await fetch(
    "https://script.google.com/macros/s/AKfycbxSJDTFAKp0fRe5ICs18LiSuSD2FGQeeH_nykj7MuAM5Rk9ThCzWMgfhSHaPR0nQoQgJA/exec"
  );

  const data = await response.json();
  return data;
}

The fetch request is similar to the direct web fetch, although the JSON is parsed.

Upon deploying the finished weblink, it will remain active for 7 days unless the App is published. Publishing can be achieved by associating the app script with a Google App Script cloud project and publishing the project. If the project is never going to face the public, like this one as it’s only ever going to access this one file, you don’t need to go through Google's verification process.

Pros of using Google Apps Script web link

Like the direct web fetch, the Google Apps Script web link requires a minimal amount of coding to pass JSONs to the webpage. The data passed can be tailored and therefore instead of the whole sheet being passed

Compared to the Direct fetch, the sheet is more protected in the Apps Script, as the link only goes to a webpage displaying the JSON, although the web link still needs to be accessible by all unless OAuth 2.0 is used. This means that an individual sheet could be used to have multiple different fetches initiated by individual Apps Scripts if needed.

Also the Apps Script web link can be used to deliver Post requests if required via doPost() functions, making it a more versatile solution than a simple webfetch. While the post address is exposed to the public, the content can be protected as the sheet doesn’t need to be shared with the public like the direct fetch does.

While the web link can only provide a JSON, and while this is setup as a JSON, the Apps Script is capable of returning ATOM, CSV, iCal, JavaScript, RSS, vCard, and XMLs in place of JSON, making it more versatile.

While this blog post is mainly about Next.js, if using fetch with React, Apps Script Web link allows for some security of the database. As React pushes all information to the client, including OAuth 2.0 details, it’s not possible to use more robust security, so using Apps Script as a CMS to access Google drive/sheets/docs becomes the most effective way without setting up a server.

Cons of using Google Apps Script web link

There are three drawbacks to the Google Apps Script approach to data fetching.

The first revolves around data security, web links are still open to public, and while they can be secured by OAuth 2.0 there’s no Droid support for Google Apps Script, what this means is that even a secured web link can either be accessed by anyone with a Google web account or only your own Google web account. While Next.js can prevent credentials being passed to end users, having to use your actual account increases the potential severity of a data breach over a droids credentials.

React based applications can’t use OAuth 2.0 without some form of middle management handling the requests as all of the React code, including credentials is passed to the client.

The final issue is splitting the code from the Next.js repository. Google Apps Script doesn’t naturally support Github, and while there are a few 3rd party applications and chrome addons that can provide Github/Bitbucket activity, you are therefore reliant upon additional dependencies, or manual uploading to Github for code repository storage and version history. While not insurmountable, this can cause additional bookkeeping for projects.

Google API fetch

The final method is using the Google API to directly interact with Google Cloud Services via API requests. This is similar to the Apps Script methodology with many similar features, although some of the shorthand support of Apps Script isn’t available, making a deeper understanding of the data structure required to pull the correct information out of the information provided.

In order to use the Google API, first start by installing the googleapi npm package, this package is highly popular, well maintained and includes auth as part, greatly streamlining the whole process.

Next a Google Cloud Platform project was created, and the sheets API was activated.

Unlike the Apps Script this comes with droid support, so I setup a droid and created OAuth2.0 credentials. Although not needed because the sheet is publicly available, should you wish to have a private sheet, the droid could still access via its OAuth2.0.

Once the OAuth2.0 credentials are made they’re stored in a secrets.json file where they can be called by the googleapi, making the initial fetch relatively straightforward.

// Get sheet function with auth and scopes provides an array of arrays
async function fetchGoogleAPI() {
  const auth = await google.auth.getClient({
    scopes: ["https://www.googleapis.com/auth/spreadsheets.readonly"],
  });

  const sheets = google.sheets({ version: "v4", auth });

googleapi fetch has scopes, as a general rule you want to use the least amount of scopes required for the specific call, in this case we only need readonly for spreadsheets. The google.auth.getClient() handles the OAuth2.0 credentials and the google.sheets handles the specific fetch requests for the Sheets API. If we were using Docs or Drive, then the corresponding docs/drive should be used.

After this the query is set up, here we’re getting the whole sheet, so the range is just the page1. The spreadsheet id is the same as before, and the response is generated as below

  // Query
  const range = `page1`;
  const response = await sheets.spreadsheets.values.get({
    spreadsheetId: "1xAY6jBd5cJPIF3UfnsDveUZL-f_qNShGb3WFQItq-vw",
    range,
  });

The response is an object with multiple potentially useful keys, however the one we’re interested in in this particular instance is the data key, which produces this:

data: {

range: 'page1!A1:Z1001',

majorDimension: 'ROWS',

values: [ [Array], [Array], [Array], [Array] ]

},

Within the data is the values, getting the values gives the same array of arrays that the other two responses have produced. This means that we can relatively easily generate the same response with the three different fetches.

  // Result
  const data = response.data;
  const returnValue = data.values

  return returnValue;
}

This leads with the final complete fetch code being:

// Get sheet function with auth and scopes provides an array of arrays
async function fetchGoogleAPI() {
  const auth = await google.auth.getClient({
    scopes: ["https://www.googleapis.com/auth/spreadsheets.readonly"],
  });

  const sheets = google.sheets({ version: "v4", auth });

  // Query
  const range = `page1`;
  const response = await sheets.spreadsheets.values.get({
    spreadsheetId: "1xAY6jBd5cJPIF3UfnsDveUZL-f_qNShGb3WFQItq-vw",
    range,
  });

  // Result
  console.log(response)
  const data = response.data;
  const returnValue = data.values

  return returnValue;
}

Pros of using Google API fetch

The Google API fetch is immensely powerful and the most complete system, with Google Apps Script being able to match it by opening up the full APIs by the advanced Google services if required. The library is well documented and the tools available are robust.

The principal advantage of the Google API fetch is the OAuth2 protection, while the Google Apps Script needs an open web page for the fetch requests or a full Google account for OAuth2, Google API has droid support that provides a bit more security.

Otherwise, both Apps Script and Google API offer very similar levels of functionality, the major difference is that Apps Script can handle data restructuring prior to posting of data.

Cons of using Google API fetch

The largest problem with using the API fetch protocol is that it’s the most complicated of the three methods to setup. OAuth2, droids, a full Google Cloud Project and a non simplified environment, compared to the non-advanced Google services Apps Script has, make establishing a project a significantly higher understanding requirement than the other two fetch methods.

Conclusions

As previously mentioned, the new next App router structure in Next.js13 came with a host of changes, including streamlining how next handles data fetching. As is often the way in software design, there are multiple ways to achieve the same goal, in this case fetching data from a Google Application. Above 3 methods were described, but ultimately they offer the same thing, while requiring different levels of time investment and knowledge to achieve.

While the Direct fetch from the website is fundamentally simple to achieve, the lack of security features for me means it’s generally worthwhile to use one of the other two fetch options.

Both Google Apps Scripts environment and Google's API provide extremely robust data handling features, achieving similar goals. The major difference between the two comes down to where the data processing takes place. While Apps Script can just pass raw files via the web fetch, it has the ability to process data prior to passing to the web server, meaning that smaller data packets can be sent. On the other side of the argument, doing all the data processing on the Next.js server gives you potentially greater control over the amount of processing power committed to the data processing.

Ultimately both methods provide robust environments and which one is used can be a matter of preference when working with Next.js. As previously mentioned, if working with React alone, then the Apps Script direction is the only way to provide security as the OAuth2 secrets would be passed to the client in a React only project, or a ‘use client’ component in Next.js.

Future work

I would like to more robustly test the performance of complex data requests from Google Drive, such as taking information from Sheets and Docs data in Drive folders and combining them, to see if there is a noticeable performance difference in rendering web pages.