如何使用Google Apps Script从Google表格单元格获取错误消息?

huangapple go评论73阅读模式
英文:

How to get Error Message from a Google sheet cell using Google Apps Script?

问题

我尝试从Google表格的“A2”单元格中提取错误消息,使用spreadsheets.get Sheets v4 API方法。我找到了这个示例,并将代码修改为下面的片段。然而,它没有提供所需的输出:

function getErrorMessage(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");

  const value = sheet.getRange("A2").getDisplayValue();
  Logger.log(value);
  if (value == "#REF!") {
    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;
      Logger.log(res.error);
    }
  }
}

“A2”单元格中的错误如下所示:

如何使用Google Apps Script从Google表格单元格获取错误消息?

我想要获取的文本如下所示:

结果未自动展开,请插入更多行 (1655)。

任何指导将不胜感激。

英文:

I am trying to extract the error message from "A2" cell of Google sheet using the spreadsheets.get Sheets v4 API method. I fond this demo and modified the code to the snippet below. However it does not give the required output:

function getErrorMessage(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");

  const value = sheet.getRange("A2").getDisplayValue();
  Logger.log(value);
  if (value == "#REF!") {
    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;
      Logger.log(res.error);
    }
  }
}

The error in cell "A2" looks like this:

如何使用Google Apps Script从Google表格单元格获取错误消息?

I want to get the text saying:
> Result was not automatically expanded, please insert more rows (1655).

Any guidance would be much appreciated.

答案1

得分: 2

From:

在你的显示脚本中,似乎没有声明res。所以,以下修改如何?

To:

const error = v.effectiveValue.errorValue;
Logger.log(error);

Note:

  • 作为测试,当我在单元格"A2"中输入=A2的公式,并运行脚本以反映我的建议的修改时,Logger.log(error);在日志中显示"检测到循环依赖。要使用迭代计算解决,请参见“文件 > 设置”。"

Added:

从你的回复来看,以下修改如何?在这个修改中,直接使用单元格坐标。

function getErrorMessage() {
  const a1Notation = 'Sheet1!A2';

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  const value = ss.getRange(a1Notation).getDisplayValue();
  Logger.log(value);
  if (value == '#REF!') {
    Logger.log("I'm in");
    const obj = Sheets.Spreadsheets.get(ss.getId(), {
      ranges: [a1Notation],
      fields: 'sheets(data(rowData(values(effectiveValue(errorValue)))))',
    });
    const v = obj.sheets[0].data[0].rowData[0].values[0];
    if (
      v.hasOwnProperty("effectiveValue") &&
      v.effectiveValue.hasOwnProperty("errorValue")
    ) {
      const error = v.effectiveValue.errorValue;
      Logger.log(error);
    }
  }
}
英文:

In your showing script, it seems that res is not declared. So, how about the following modification?

From:

res.error = v.effectiveValue.errorValue;
Logger.log(res.error);

To:

const error = v.effectiveValue.errorValue;
Logger.log(error);

Note:

  • As a test, when I put a formula of =A2 into a cell "A2, and run the script by reflecting my proposed modification, Logger.log(error); showed Circular dependency detected. To resolve with iterative calculation, see File > Settings. in the log.

Added:

From your reply, how about the following modification? In this modification, the cell coordinate is directly used.

function getErrorMessage() {
  const a1Notation = "'Sheet1'!A2";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  const value = ss.getRange(a1Notation).getDisplayValue();
  Logger.log(value);
  if (value == "#REF!") {
    Logger.log("I'm in")
    const obj = Sheets.Spreadsheets.get(ss.getId(), {
      ranges: [a1Notation],
      fields: "sheets(data(rowData(values(effectiveValue(errorValue)))))",
    });
    const v = obj.sheets[0].data[0].rowData[0].values[0];
    if (
      v.hasOwnProperty("effectiveValue") &&
      v.effectiveValue.hasOwnProperty("errorValue")
    ) {
      const error = v.effectiveValue.errorValue;
      Logger.log(error);
    }
  }
}

huangapple
  • 本文由 发表于 2023年4月4日 10:50:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/75925161.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定