如何在将参考表复制到当前电子表格时更新工作表中的公式

我有两个电子表格,A和B,我正在编写一个脚本,将它们与每个业务部门的自定义项合并 .

电子表格A包含一个“计算”表,其中公式引用了一个名为“材料”的图纸,但它不包含“材料”表 .

我编写了脚本,将“材料”表从电子表格B复制到电子表格A.

var materialsSheet = spreadSheetB.getSheetByName("Materials");
materialsSheet.copyTo(spreadSheetA).setName("Materials");

但是,SpreadsheetA中“计算”表单上的公式仍然抱怨未找到“材料”表 . 如果我手动进入前端并模拟编辑,则单元格中的值会更新 .

我尝试复制公式并通过Apps-Script将它们粘贴回来,但它没有用 . 有关如何使其工作的任何建议?

var readRange = calcSheet.getRange(9,1,7,6);
readRange.copyTo(calcSheet.getRange(9,1,7,6));

另一种思路:我的猜测是“材料”表是由G表在内部使用一些引用ID引用的,而不是它的名称 . 如何以编程方式更新内部参考?

更新:我的问题被确定为另一个问题的可能重复,在其公式中使用“间接” . 我不在我的电子表格中使用“间接”,它将地址作为字符串接收并返回引用地址 . 这不是我正在寻找的功能,所以我不能将它添加到我的工作表公式 . 我确实尝试了该问题的建议解决方案(使用IFERROR),但它也没有帮助 . 我只是在出错时返回null值 .

回答(1)

2 years ago

这个解决方法怎么样?虽然我不确定这对你的情况是否有用,你能尝试一下吗?此解决方法的流程如下 .

流程:

  • 将"Materials"复制到目标表 .

  • 将"Calculations"复制到目标工作表作为"Calculations_temp"的名称 .

  • 将"Calculations_temp"的数据复制到"Calculations" .

  • 删除"Calculations_temp" .

确认:

  • 在我的环境中,我确认 SpreadsheetApp.flush() 不能用于这种情况 .

  • 在您的问题中,对于 I tried copying the formulas and pasting them back via Apps-Script, but it didn't work.

  • 确认公式不起作用,即使它复制了同一张纸的范围 .

因此,通过使用复制的“Calculations_temp”覆盖“计算”表,我尝试更新公式 .

示例脚本:

var srcId = "#####"; // File ID of "Materials"
var dstId = "#####"; // File ID of "Calculations"
var src = SpreadsheetApp.openById(srcId).getSheetByName("Materials");
var dstSheet = SpreadsheetApp.openById(dstId);

// Copy "Materials" to destination sheet.
src.copyTo(dstSheet).setName("Materials");

// Copy "Calculations" to destination sheet as the name of "Calculations_temp".
dstSheet.getSheetByName("Calculations").copyTo(dstSheet).setName("Calculations_temp");

// Copy data of "Calculations_temp" to "Calculations".
//  var range = dstSheet.getSheetByName("Calculations").getDataRange(); // I confirmed that this didn't work.
var range = dstSheet.getSheetByName("Calculations_temp").getDataRange();
range.copyTo(dstSheet.getSheetByName("Calculations").getDataRange());

// Delete "Calculations_temp".
dstSheet.deleteSheet(dstSheet.getSheetByName("Calculations_temp"));

注意:

  • 使用此项时,请输入"Materials"和"Calculations"的文件ID .

如果这对你没用,我很抱歉 .