Intro

A lot of the advanced implementations for the needs of your web application will require you to understand the platform you develop on. In our case, our Next.js web application powered by Google Spreadsheets required hundreds of subpages to access the same data in a performant way and here is how we leveraged static data caching to address it.

What our app needed

The content of our site is powered by Google spreadsheets for easy content management without any technical knowledge. Using Sheets API, however, presents some constraints such as API quota limits per minute. On top of this, the querying mechanism provided by the Sheets API is not as nearly as robust as a traditional database, so we had to find another way.

To sum up our situation:

• The pages are static, meaning they are not personalized for the user and the content is available before the page is requested,
• We have hundreds of details pages that require a small portion of the big snapshot of data, but there is no way to query just that,
• Sheets API is slow to fetch and parse, and has per minute quotas that won’t scale well

Solution

Generating Static Pages

First thing to address was to generate static pages for all of our content. This was solving 2 problems at once, we would not need to query the Sheets for every new request which would solve the quota problem and the pages would be cached and instantly available.

Although sounds very ideal, the implementation was not all that easy. For each of the hundreds of static pages, Next.js’s getStaticProps function would still call the API in build time and exceed the quota, and get the same global snapshot of the data for no reason. So we needed a way to cache data locally in the web server. This was accomplished easily by programming a simple caching logic to the fetch, allowing subsequent calls to the resource to use the cached json data that was available in the web server.

export async function getCachedResources(): Promise<Resource[]> {
  let cachedData;
  try {
    cachedData = JSON.parse(fs.readFileSync(resourceJsonPath(), "utf-8"));
  } catch (err) {
    console.log("Unable to get resource cache.");
  }
  if (!cachedData) {
    console.log("No cache found, reading from Spreadsheet");
    cachedData = await getResources();
    cacheResourceData(cachedData);
  }
  return cachedData;
}

A simple example on this concept can also be found in this Vercel tutorial, but what we wanted is a little different from here. While this example uses static data that does not change between build, our static data must update when the spreadsheet is changed.

Using this caching mechanism along with getStaticPaths() and getStaticProps() was all we needed to generate hundreds of pages with a single call to the Sheets API and make all pages instantly available to the users regardless of the load.

Triggering fresh data on Spreadsheet edit

Another challenge was to make our website builds event driven so that the website would only be rebuilt if the content changed. We used Google Apps Script along with Vercel’s Deploy Hooks to trigger a complete rebuild of the website whenever the content of the sheets changed. This was a fairly simple process to set up but did not give a granular control over which pages to be rebuilt. It would simply rebuild all the pages in the website.

Remarks

We like to note that we did experiment with Incremental Site Generation, which allows us to revalidate only selected pages of the website upon a trigger, however this approach does not allow statically cached data (like the resources data we used) to be modified, so it wasn’t a viable option for our case.

Knowing the platform and utilizing Next’s capabilities helped us deliver a better content management, performant and scalable web service and most importantly a near perfect user experience for our case, which reminded me how advanced implementations leverage the platform they use.