Reads the row values of the specified Google Sheet that was published to the web without needing an API key.

readGSheetValues.js

/**
 * Reads the row values of the specified Google Sheet that was published to the
 * web without needing an API key.
 * @param {string} publishedURL
 *   The URL of the sheet that you want to read from as copied from the "Publish
 *   to the Web" modal in Google Sheets.  To get the contents of a sheet other
 *   than the first one you must use the URL of that sheet when selecting it in
 *   the "Publish to the Web" modal in Google Sheets.
 * @returns {Promise<(boolean|null|number|string)[][]>}
 */
async function readGSheetValues(publishedURL) {
  // Attempt to parse the published URL.
  let parsedURL = /^(https:.+\/[^/]{40,}\/).+?(?:[?&]gid=([^&]+).*)?$/.exec(publishedURL);

  // Throw an error if the given URL was not able to be parsed.
  if (!parsedURL) {
    throw new Error(`The given "Publish to the Web" URL is not recognized:\n${publishedURL}`);
  }

  // Get the CSV content from Google.
  let urlToFetch = `${parsedURL[1]}pub?output=csv&gid=${parsedURL[2] ?? 0}`;
  const csvContent = await(await fetch(urlToFetch)).text();

  // Turn the CSV content into an array of arrays and return it.
  return[...csvContent.matchAll(/("(?:""|[^"]+)*"|[^,\r\n]*)(,|\r?\n|$)/g)].reduce(
    (rows, [_, content, sep]) => {
      if (sep || content) {
        const currentRow = rows[rows.length - 1];
        // If the next separator was given and is not a comma that means that
        // the next value should go onto a new row.
        if (sep && sep !== ',') rows.push([]);
        // Turn the content into the property type and then add it to the
        // current row.
        if (content === 'TRUE') content = true;
        else if (content === 'FALSE') content = false;
        else if (content === '') content = null;
        else if (content.charAt(0) === '"') content = content.slice(1, -1).replace(/""/g, '"');
        else if (/^-?\d+(?:\.\d+(?:E[-+]\d+)?)?$/.test(content)) {
          const numContent = parseFloat(content);
          if (numContent === numContent && isFinite(numContent)) content = numContent;
        }
        currentRow.push(content);
      }
      return rows;
    },
    [[]]
  );
}

~example-bible-timeline-data.js

console.log(await readGSheetValues("https://docs.google.com/spreadsheets/d/e/2PACX-1vQWC-240krTcgSxCROykbgdpK9LyCbJECdmYNJtLPII1VLYzvMTWGZSnT_5wbxlLzWI6aQa5Mjm5ul8/pubhtml?gid=0&single=true"));

~example-cwest.net-search.js

console.log(await readGSheetValues("https://docs.google.com/spreadsheets/d/e/2PACX-1vSyMpfAC0eB1vOJ9k1bg2NfsMjBEJgHmLn0Z2-dTvWYpqgb2vzNhLuqjZrt6r33BHKSdLgNj03r9clX/pub?gid=0&single=true&output=csv"));
// console.log(await readGSheetValues("https://docs.google.com/spreadsheets/d/e/2PACX-1vSyMpfAC0eB1vOJ9k1bg2NfsMjBEJgHmLn0Z2-dTvWYpqgb2vzNhLuqjZrt6r33BHKSdLgNj03r9clX/pubhtml"));
// console.log(await readGSheetValues("https://docs.google.com/spreadsheets/d/e/2PACX-1vSyMpfAC0eB1vOJ9k1bg2NfsMjBEJgHmLn0Z2-dTvWYpqgb2vzNhLuqjZrt6r33BHKSdLgNj03r9clX/pub?output=csv"));
// console.log(await readGSheetValues("https://docs.google.com/spreadsheets/d/e/2PACX-1vSyMpfAC0eB1vOJ9k1bg2NfsMjBEJgHmLn0Z2-dTvWYpqgb2vzNhLuqjZrt6r33BHKSdLgNj03r9clX/pub?gid=1228768345&single=true&output=csv"));