CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
reraisebob
reraisebob

Reputation: 29

Getting the error note of a formula with #NA in google sheets for a script

I cant find any way of getting the error message of a failing formula (which actually gives more info that just the error code since this is always 7 for #NA). #NA could mean different things and I would like to be able to identify which cells have which type of #NA.

getNote does not work.

Is there a way?

Im using IMPORTXML(C1:C,"//@value")

but for example when I vlookup is failing this is the error message that I would like to retrieve

enter image description here

Upvotes: 2

Views: 375

Answers (1)

Iamblichus
Iamblichus

Reputation: 19339

Update 2021-02-01:

As mentioned by Tanaike, 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 = "A1") {
  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;
  return errorMessage;
}

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

Original answer:

As far as I know, there is no built-in function, no Apps Script tool and no API method that will retrieve this information.

If you think it would be considerably useful to have this functionality, you could consider creating a Feature Request in Google Issue Tracker.

As a workaround, you could create a custom function for each of the built-in functions you are using and use try... catch to retrieve whatever exceptions you get.

I hope this is of any help.

Upvotes: 2

Related Questions