英文:
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”单元格中的错误如下所示:
我想要获取的文本如下所示:
结果未自动展开,请插入更多行 (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:
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);
showedCircular 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);
}
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论