Retrieving All Values from All Sheets from URL of 2PACX- of Web Published Google Spreadsheet using Google Apps Script and Javascript

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a sample script for retrieving all values from all sheets from URL of 2PACX- of Web Published Google Spreadsheet using Google Apps Script and Javascript.

In this post, it supposes that the Google Spreadsheet has already been published for Web. Ref

Flow

The flow of this method is as follows.

  1. From the client side, send the URL of web published Google Spreadsheet to the Web Apps created by Google Apps Script.
    • The URL is like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml.
  2. Return the byte array of XLSX data from the Web Apps to the client side.
  3. At the client side, the XLSX data is parsed with SheetJS js-xlsx.
  4. Retrieve all values from all sheets.

Usage

1. Prepare script.

Please copy and paste the following script to the script editor and save it.

const doGet = (e) => {
  let url = e.parameter.url;
  if (
    url &&
    /https:\/\/docs\.google\.com\/spreadsheets\/d\/e\/2PACX-.+\/pub(html)?/.test(
      url
    )
  ) {
    const temp = url.split("/");
    temp.pop();
    url = temp.join("/") + "/pub?output=xlsx";
    return ContentService.createTextOutput(
      JSON.stringify({
        bytes: [
          ...Uint8Array.from(UrlFetchApp.fetch(url).getBlob().getBytes()),
        ],
      })
    );
  }
  return ContentService.createTextOutput(JSON.stringify({ bytes: [] }));
};

2. Deploy Web Apps.

  1. On the script editor, Open a dialog box by “Publish” -> “Deploy as web app”.

  2. Select “Me” for “Execute the app as:”.

    • By this, the script is run as the owner.

  3. Select “Anyone, even anonymous” for “Who has access to the app:”.

  4. Click “Deploy” button as new “Project version”.

  5. Automatically open a dialog box of “Authorization required”.

    1. Click “Review Permissions”.
    2. Select own account.
    3. Click “Advanced” at “This app isn’t verified”.
    4. Click “Go to ### project name ###(unsafe)”
    5. Click “Allow” button.

  6. Click “OK”.

  7. Copy the URL of Web Apps. It’s like https://script.google.com/macros/s/###/exec.

    • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

4. Test this method.

Please set spreadsheetUrl and webAppsUrl, and run the script. By this, all values from all sheets are retrieved from the web published Google Spreadsheet. In this case, HTML file of the client side can be also put to the outside of Google Drive.

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.15.1/shim.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.15.1/xlsx.full.min.js"></script>

<button type="button" onclick="main()">Run</button>

<script>
  function main() {
    const spreadsheetUrl =
      "https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml";
    const webAppsUrl =
      "https://script.google.com/macros/s/###/exec?url=" + spreadsheetUrl;

    fetch(webAppsUrl)
      .then((res) => res.json())
      .then(({ bytes }) => {
        const workbook = XLSX.read(new Uint8Array(bytes), { type: "array" });
        const res = Object.entries(workbook.Sheets).map(([k, v]) => ({
          sheetName: k,
          values: XLSX.utils.sheet_to_json(v, { header: 1 }),
        }));
        console.log(res); // You can see the result at the console.
      })
      .catch((err) => console.error(err));
  }
</script>

Result

When above script is run, the following sample value is returned.

[
  {
    "sheetName": "Sheet1",
    "values": [["a1", "b1", "c1"], ["a2", "b2", "c2"], , ,]
  },
  {
    "sheetName": "Sheet2",
    "values": [["a1", "b1", "c1"], ["a2", "b2", "c2"], , ,]
  },
  ,
  ,
]

References

 Share!