如何使用Google Spreadsheet中的ImportRange函数强制重新计算单元格？ [英] How can I force a recalculation of cell using ImportRange function in a Google Spreadsheet?

问题描述

我有一个电子表格Target，它使用ImportRange从另一个电子表格Source中获取一些数据，如

  A6 = query ImportRange（mykey，Weekly！B：BI），CONCATENATE（select * WHERE Col1 ='，B3，'），0）
  

select子句用于选择与B3中的值匹配的数据 - 如果此单元格值已更改，则导入将立即更新。

如果有人在Source电子表格中更改了某些内容，我们希望能够强制更新，以便我们可以在立即中看到它反映在Target中。目前这种情况只发生在一段时间以后的某个不确定的时间之后，而且对我的目的来说太慢了。

更新：

根据下面的回答和评论，我在Source中添加了一个函数UpdateTarget，该函数从on编辑触发器*中调用：

<$ p $函数UpdateTarget（）{
try {
var ss = SpreadsheetApp.openById（targetID）;
}
catch（err）
{
Browser.msgBox（err）;
}
var sheet = ss.getSheetByName（Weekly）;
sheet.getRange（A4）。setValue（= query（ImportRange（sourceID，Weekly！B：BI），CONCATENATE（select * WHERE Col1 ='，B3，' ），0））;
SpreadsheetApp.flush（）;
}

这似乎更有效，但我也可以编写脚本如果我要沿着这条路线前进，更新后的值直接进入目标。

我真的希望Target成为其他用户可以复制的模板，并且可以从主源中提取特定的实时数据，但我不愿意在当需要添加新的目标时需要维护的源代码。

我认为我真正需要的是重新陈述公式的方式，以便它取决于可以编辑的单元格并触发重新计算 - 但对结果没有任何影响？

（*另外：我发现openByID函数返回Action not allowed异常，除非通过显式触发器调用 - onEdit禁止编辑其他电子表格 - 请参阅 https://developers.google.com/apps-script/understanding_triggers ）。

解决方案

当用户打开电子表格或者单元格电子表格）被改变，影响公式。

由于您使用两种不同的电子表格，后一种情况不适用。

从哪个电子表格调用flush（）函数。我假设它在目标电子表格中。这将是没用的。

一种解决方案是在源电子表格中编写脚本，每次编辑源文件时都会修改目标电子表格

I have a spreadsheet Target that uses ImportRange to get some data from another spreadsheet Source, as in

A6 = query(ImportRange("mykey", "Weekly!B:BI"), CONCATENATE("select * WHERE Col1='",B3,"'"), 0)

The select clause is to sub-select the data that matches on a value in B3 - if this cell value is changed then the import is updated "immediately".

I want to be able to force an update if someone changes something in the Source spreadsheet so that we see it reflected in the Target "immediately". Currently this only happens "after some unspecified time" which is up to a minute or so, and too slow for my purposes.

Update:

As per the answer and comments below, I added a function UpdateTarget in the Source that is called from a on edit trigger*:

function UpdateTarget() {
  try {
    var ss = SpreadsheetApp.openById("targetID");
  }
  catch(err)
  {
    Browser.msgBox(err);
  }
  var sheet = ss.getSheetByName("Weekly");
  sheet.getRange("A4").setValue("=query(ImportRange("sourceID", "Weekly!B:BI"), CONCATENATE("select * WHERE Col1='",B3,"'"), 0) ");
  SpreadsheetApp.flush();
}

This seems to work more effectively but I may as well have a script to write the updated values directly into the target if I'm going down this route.

I really wanted the Target to be a template which other users could duplicate and which would extract specific "live" data from a master Source, I am reluctant to implement scripts in the Source that would need maintaining as new Targets are added.

I think what I really need is a way of re-stating the formula such that it depends on a cell which can be edited and will trigger a re-calc - but the has no effect on the result?

(*An aside: I found that the openByID function returned "Action not allowed" exception unless its called via an explicit trigger - onEdit is forbidden from editing other spreadsheets -see https://developers.google.com/apps-script/understanding_triggers).

解决方案

Formulas are recalculated when the spreadsheet is opened by a user or when a cell (within the same spreadsheet) is changed that affects the formula.

Since you are working with two different spreadsheets, the latter case is not applicable.

From which spreadsheet are you calling the flush() function. I assume it is in the target spreadsheet. This will be of no use.

One solution would be to write a script in the source spreadsheet that will modify the target spreadsheet each time the source is edited

这篇关于如何使用Google Spreadsheet中的ImportRange函数强制重新计算单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！