有没有更快的方法来刷新我的所有自定义公式? [英] Is there a faster way to refresh all of my custom formulas?
本文介绍了有没有更快的方法来刷新我的所有自定义公式?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要通过Google表格中的脚本刷新所有自定义公式,但这似乎要花很多时间(例如,每100个单元格需要30秒).自定义公式可能会包含成千上万个单元格,因此我必须想出一种更好的方法.我有:
I need to refresh all of my custom formulas via a script in Google Sheets but this seems to take forever (like, 30 seconds for 100 cells). There will potentially be thousands of cells with my custom formula so I must come up with a better way. I have:
function refresher(){
var sheet = SpreadsheetApp.getActiveSheet();
var selection = sheet.getDataRange();
var columns = selection.getNumColumns();
var rows = selection.getNumRows();
for (var column=1; column <= columns; column++){
for (var row=1; row <= rows; row++){
var cell=selection.getCell(row,column);
var formula = cell.getFormula();
if (formula.startsWith("=myfunc(")){
cell.setFormula(formula.replace("=myfunc(", "?myfunc("));
}
}
}
SpreadsheetApp.flush();
for (var column=1; column <= columns; column++){
for (var row=1; row <= rows; row++){
var cell=selection.getCell(row,column);
var formula = cell.getFormula();
if (formula.startsWith("=?myfunc(")){
cell.setFormula(formula.replace("=?myfunc(", "=myfunc("));
}
}
}
}
推荐答案
为了实现您的目标,如何使用TextFinder?在这种情况下,我认为可以降低工艺成本.在脚本中,当使用TextFinder时,情况如下所示.
In order to achieve your goal, how about using TextFinder? In this case, I think that the process cost might be able to be reduced. From your script, when TextFinder is used for your situation, it becomes as follows.
function refresher() {
const sheet = SpreadsheetApp.getActiveSheet();
const formula = "=myfunc";
const tempFormula = "=sampleFormula";
sheet.createTextFinder("^\\" + formula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(tempFormula);
sheet.createTextFinder("^\\" + tempFormula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);
}
参考:
- Class TextFinder
- Class TextFinder
Reference:
这篇关于有没有更快的方法来刷新我的所有自定义公式?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文