How can I cache this call to the Google Sheets API?

Question

I have followed the instructions here to use a Google Sheet as a JSON endpoint. I am then using that data in Eleventy. This code is currently working:

module.exports = async function() {
  let url = `https://docs.google.com/spreadsheets/d/${process.env.GOOGLE_SHEETS_ID}/gviz/tq?tqx=out:json`;
  console.log("Fetching from Google Sheets...");
  return await fetch(url)
    .then(res => res.text()) // node-fetch option to transform to json
    .then(text => {
      let json = JSON.parse(text.substr(47).slice(0, -2));
      return {
        items: json.table.rows
      };
    });
}

...however, this is making for slow build times, so I am trying to tie this in with the eleventy-cache-assets plugin as described in the 11ty docs.

Here is what I've tried:

module.exports = async function() {
  let url = `https://docs.google.com/spreadsheets/d/${process.env.GOOGLE_SHEETS_ID}/gviz/tq?tqx=out:json`;

  var text = await Cache(url, {
    duration: "1s",
    type: "text"
  })
  .then(text => {
    var json = JSON.parse(text.substr(47).slice(0, -2));
    console.log(json);
    return {
      items: json.table.rows
    };
  });
};

In the console, it does return the JSON, but then when I try to make a collection out of the data in .eleventy.js like this:

eleventyConfig.addCollection("myGSheets", (collection) => {
  return collection.getAll()[0].data.myGSheets.items;
});

I get an error: Cannot read property 'items' of undefined

I'm not sure what is happening in between the JSON data appearing in the console, and then being undefined.

I am guessing maybe I need to do the string manipulation of the response before calling Cache, perhaps? I'm just not sure how to put it all together...

Answer 1

It looks like you're not actually returning anything from your data function, since your return statement is inside the callback function, not the top-level data function.

module.exports = async function() { // <= top level data function
  let url = `https://docs.google.com/spreadsheets/d/${process.env.GOOGLE_SHEETS_ID}/gviz/tq?tqx=out:json`;

  var text = await Cache(url, {
    duration: "1s",
    type: "text"
  })
  .then(text => { // <= inner callback function
    var json = JSON.parse(text.substr(47).slice(0, -2));
    console.log(json);
    return { // <= This return statement returns from the inner callback
      items: json.table.rows
    };
  });

  // <= This function doesn't return anything!
};

Since you're using async/await, there isn't a need for using Promise.then. You can just await the promises, which prevents needing all the callbacks.

Try:

module.exports = async function() {
  let url = `https://docs.google.com/spreadsheets/d/${process.env.GOOGLE_SHEETS_ID}/gviz/tq?tqx=out:json`;

  var text = await Cache(url, {
    duration: "1s",
    type: "text"
  })

  var json = JSON.parse(text.substr(47).slice(0, -2));
  console.log(json);
  return {
    items: json.table.rows
  };
};