CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
brazoayeye
brazoayeye

Reputation: 331

Read error description from a #N/A in a google Sheet

In google sheets, I have different scenarios that gives #N/A error but from interface I can see that the error is not exactly the same since the description is different from case to case.

As an example two formulas

=IMPORTXML("https://www.google.com"; "//asd")

and

=IMPORTXML("https://www.google.comn"; "//asd")

gives both #N/A but first is "Empty value in the fetched document", the second is "unable to fetch". Is there a way to distinguish the two cases from the script side? I would like to avoid to fetch using UrlFechApp because fetched URLs always have captcha and anti-bot pages when fetched.

Upvotes: 1

Views: 144

Answers (1)

NightEye
NightEye

Reputation: 11214

As mentioned by Tanaike and lamblichus, error messages can be retrieved in Sheets API via spreadsheets.get. This information can be found as part of the ExtendedValue, which corresponds to the property effectiveValue of the cell data.

An example of a response could be the following one:

{
  "sheets": [
    {
      "data": [
        {
          "rowData": [
            {
              "values": [
                {
                  "effectiveValue": {
                    "errorValue": {
                      "type": "DIVIDE_BY_ZERO",
                      "message": "Function DIVIDE parameter 2 cannot be zero."
                    }
                  }
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

You could then use Advanced Sheet Service to access this API via Apps Script. For example, if you wanted to retrieve the error message for cell A1, you could do the following:

function getErrorMessage(cellNotation) {
  const optionalArgs = {
    fields: "sheets(data(rowData(values(effectiveValue))))",
    ranges: cellNotation
  }
  const spreadsheetId = SpreadsheetApp.getActive().getId();
  const response = Sheets.Spreadsheets.get(spreadsheetId, optionalArgs);
  const errorMessage = response.sheets[0].data[0].rowData[0].values[0].effectiveValue.errorValue.message;
  const errorType = response.sheets[0].data[0].rowData[0].values[0].effectiveValue.errorValue.type;
  Logger.log(errorType);
  Logger.log(errorMessage);
}

function main() {
  getErrorMessage("A1");
  getErrorMessage("A2");
}

Sample input:

input1 input2

Sample output:

output

Note:

  • When I tried your sample, both does return the same error. Not sure why it isn't the same with yours but the script output was exactly the same as sheets cell error message.

Unfortunately, this cannot be used as a custom function since it requires user authorization (see Using Apps Script services).

Upvotes: 1

Related Questions