Skip to content

Instantly share code, notes, and snippets.

@tanaikech

tanaikech/submit.md

Last active Feb 2, 2021
Embed
What would you like to do?
XPath Tester using Web Apps Created by Google Apps Script

XPath Tester using Web Apps Created by Google Apps Script

In this post, I would like to introduce the xpath tester using Web Apps created by Google Apps Script.

Demo

Usage

1. Prepare Google Spreadsheet.

Please create new Google Spreadsheet on your Google Drive.

2. Enable Sheets API.

Please open the script editor at the created new Spreadsheet and enable Sheets API at Advanced Google services.

3. Prepare sample script.

Please copy and paste the following script to the script editor on the created new Spreadsheet and save it.

function doGet(e) {
  const url = e.parameter.url;
  const xpath = e.parameter.xpath;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  sheet.clear();
  const range = sheet.getRange("A1");
  range.setFormula(`=IMPORTXML("${url}";"${xpath}")`);
  do {
    SpreadsheetApp.flush();
    Utilities.sleep(1000);
  } while (range.getDisplayValue() == "Loading...");
  const values = sheet.getDataRange().getDisplayValues();
  const res = { url: url, xpath: xpath, values: values };
  if (values[0][0] == "#N/A") {
    const obj = Sheets.Spreadsheets.get(ss.getId(), {
      ranges: [sheet.getSheetName()],
      fields: "sheets",
    });
    const v = obj.sheets[0].data[0].rowData[0].values[0];
    if (
      v.hasOwnProperty("effectiveValue") &&
      v.effectiveValue.hasOwnProperty("errorValue")
    ) {
      res.error = v.effectiveValue.errorValue;
    }
  }
  sheet.clear();
  return ContentService.createTextOutput(JSON.stringify(res)).setMimeType(
    ContentService.MimeType.JSON
  );
}

4. Deploy Web Apps.

The detail information can be seen at the official document.

  1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
  2. Please click "Select type" -> "Web App".
  3. Please input the information about the Web App in the fields under "Deployment configuration".
  4. Please select "Me" for "Execute as".
  5. Please select "Anyone" for "Who has access".
    • By this setting, anyone can access to your Web Apps. In this case, it's for testing this script.
    • Of course, you can use the access token for this situation. But in this case, as a simple setting, I use the access key instead of the access token.
  6. Please click "Deploy" button.
  7. When "The Web App requires you to authorize access to your data" is shown, please click "Authorize access".
  8. Automatically open a dialog box of "Authorization required".
    1. Select own account.
    2. Click "Advanced" at "This app isn't verified".
    3. Click "Go to ### project name ###(unsafe)"
    4. Click "Allow" button.
  9. Copy the URL of Web App. 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.

5. Testing.

In order to test this, as a sample, the curl command is used as follows. https://script.google.com/macros/s/###/exec is the URL of your Web Apps.

$ curl -L 'https://script.google.com/macros/s/###/exec?url=https://tanaikech.github.io/index.xml&xpath=//channel/title'

In this case, the value from the formula of =IMPORTXML("https://tanaikech.github.io/index.xml","//channel/title") is returned. When the following result can be seen, it means that above setting works.

{
  "url": "https://tanaikech.github.io/index.xml",
  "xpath": "//channel/title",
  "values": [["tanaike"]]
}

For example, when the xpath occurs an error, the following result is returned.

$ curl -L 'https://script.google.com/macros/s/###/exec?url=https://tanaikech.github.io/index.xml&xpath=//channel/sample'
{
  "url": "https://tanaikech.github.io/index.xml",
  "xpath": "//channel/sample",
  "values": [["#N/A"]],
  "error": { "type": "N_A", "message": "Imported content is empty." }
}

The error message from the formula can be retrieve the method of spreadsheets.get in Sheets API.

Note

  • When you want to use this at other languages, the values of query parameter of url and xpath might be required to be URL encoded. Please be careful this.

Reference

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment