在Google表格中运行公式计算而无需打开文件 [英] Run formula calculation in Google Sheets without to open file

问题描述

我有一个计划的插件，该插件会定期将数据写入Google表格，而无需打开文件.我有一个公式，该公式应该对写入的数据进行一些计算并将计算出的值写入工作表中，因此，以该工作表为数据源的Data Studio会更新可视化效果.

I have a scheduled addon, which writes data periodically into a Google Sheet, without to open file. I have a formula, which should make some calculations on written data and write calculated values into the sheet, so then Data Studio with this sheet as data source updates the visualization.

没有打开文件之类的手动步骤，定期运行公式计算和写入计算值的方法是什么?

What is the way to periodically run a formula calculation and write calculated values without manual steps like opening file?

我对这个话题还很陌生:我的搜索方法找到了诸如将公式转换为应用程序脚本或计划公式执行之类的东西，这使我没有任何收获.

I'm pretty new to this topic: my search approaches to find something like convert formula to app script or schedule formula execution brought me not to any fruitful idea.

PS_应该执行计算的公式是:

PS_ the formula, which should do the calculation is:

=IFERROR(((VLOOKUP(A2,'[1]SV'!$A:$B,2.0))/B2)+(IF(B2=1,"33,9",IF(B2=2,"16,28",IF(B2=3,"10,36",IF(B2=4,"7",IF(B2=5,"5,64")))))+IF(B2=6,"4,13",IF(B2=7,"3,27",IF(B2=8,"2,61",IF(B2=9,"2,18",IF(B2=10,"1,82")))))+IF(B2=11,"1,77",IF(B2=12,"1,81",IF(B2=13,"1,85",IF(B2=14,"1,9",IF(B2=15,"2,04")))))+IF(B2=16,"1,68",IF(B2=17,"1,61",IF(B2=18,"1,65",IF(B2=19,"1,62",IF(B2=20,"1,59","0")))))),0)

如果我知道如何将公式转换为应用程序脚本，我将管理其余部分-我熟悉基于时间的触发器的运行脚本.或者，也许有一种以相同的预定方式运行公式的方法，例如脚本...?

If i would know, how to convert the formula to app script, i would manage the rest - i'm familiar with running scripts with time based trigger. Or, maybe, there is a method to run formula on the same scheduled way, like scripts...?

推荐答案

*免责声明:我没有尝试过，它可能不起作用，如果不能，请发表评论，我将尝试提供另一种解决方案

好的，这就是我会尝试的方法: 在Google脚本中，有一个触发功能，通常用于测试.它的作用是运行脚本并在出现错误时向您发送电子邮件，并且假设您可以使用它定期运行脚本.

*Disclaimer: I have not tried this and it might not work, if it doesn't, comment and I will try to provide another solution

OK here's what i would try: In google scripts there is a trigger function, manly used for testing. what it does is it runs your script and sends you an email if there is a error, and hypothetically, you could use this to run you script periodically.

如果这不起作用，我可以尝试其他几件事，如果您希望我深入了解这些内容，请发表评论:

A couple of other things I may try if this doesn't work, leave a comment if you would like me to go in depth about these:

根据您希望它如何工作，您可以设置一个网络应用程序来执行此操作，而不是用谷歌表格打开脚本，而是使用脚本中内置的计时器打开该网络应用程序，只要您拥有网页已打开.

depending on how you want this to work, you could set up a web app to do this, and instead of opining google sheets open the web app with a timer built into the script, that would trigger as long as you had the web page opened.

还可能需要使用JavaScript函数自动运行脚本，需要进行更多研究.

It might also be possible to run the script automatically with a JavaScript function, requires more research.

这篇关于在Google表格中运行公式计算而无需打开文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！