google-apps-script - Google表格 - 替换数组中公式的特定部分
问题描述
在复制到新范围之前,我正在尝试编写一个脚本以将某些公式的引用替换为它们的当前值。
我想保留公式的其余部分,这样我就可以在不使用新工作表的情况下存储值的历史记录。
我尝试了这段代码,但公式中没有发生任何变化:
function repformulas() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var formrange = sheet.getRange("h1:h6");
var formulas = formrange.getFormulas();
var reprange = sheet.getRange("c2:c6");
var pasterange = sheet.getRange("i1:i6");
var regexp = /(.{2})\s./g;
for (var i = 0; i < formulas.length; i++) {
for (var j = 0; j < formulas[0].length; j++) {
formulas[i][j] = formulas[i][j].replace(regexp, repvalue[i][j]);
}
}
sheet.insertColumns(9, 1);
pasterange.setFormulas(formulas);
}
我在公式数组中有这个:
["=eomonth(I1;1)"] ["=($C$2/3869,01-1)*100"] ["=($C$3/4625,68-1)*100"] ["=($C$4/8494.69-1)*100"] ["=($C$5/15056.32-1)*100"] ["=($C$6/23540,01-1)*100"]
我想用这个替换它:
["=eomonth(I1; 1)"] ["=(4001.02/3869.01-1)*100"] ["=(4805.08/4625.68-1)*100"] ["=(8399.84/8494.69-1)*100"] ["=(15057.98/15056.32-1)*100"] ["=(23554.56/23540.01-1)*100"]
如果有人可以帮助我,我将不胜感激。
解决方案
- 您想通过将 a1Notations 转换为值来放置公式。
I1
of=eomonth(I1;1)
是日期对象。您想保留它并转换其他 a1Notations。- 从你的回复来看,是
Decimals are separated by comma in my country.
。 - 您想使用 Google Apps 脚本实现此目的。
如果我的理解是正确的,那么这个修改呢?该修改的流程如下。
流动:
- 分别从 和 的单元格中检索公式和
H1:H6
值C1:H6
。 - 检索 的值
C2, C3, C4, C5, C6, H1
。 - 转换检索到的公式。
,
被修改为.
。$C$2
、$C$3
、$C$4
和$C$5
的a1 符号$C$6
被修改为从“C2”到“C6”的值。- 在这种情况下,
=eomonth(I1;1)
转换为当前值。所以可以保存这个值。sheet.insertColumns(9, 1)
运行时,ofI1
更新=eomonth(I1;1)
为J1
. 我以为这个可以用。
- 在“I”列中插入一列。
- 将转换后的公式放入
I1:I6
插入的单元格中。
修改后的脚本:
请按如下方式修改您的脚本。
function repformulas() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var formrange = sheet.getRange("h1:h6");
var formulas = formrange.getFormulas();
// --- I modified below script
var [[_, _, _, _, _, H1], [C2], [C3], [C4], [C5], [C6]] = sheet.getRange("C1:H6").getValues();
var obj = {C2: C2, C3: C3, C4: C4, C5: C5, C6: C6};
var converted = formulas.map(function(e, i) {
return i == 0 ? [H1] : [e[0].replace(/,/g, ".").replace(/\$[A-Z]\$\d+/, function(m) {return obj[m.replace(/\$/g, "")]})];
});
// ---
var pasterange = sheet.getRange("i1:i6");
sheet.insertColumns(9, 1);
pasterange.setFormulas(converted);
}
参考:
推荐阅读
- java - OkHttp 这个浏览器不支持框架
- vb.net - 如何在 vb 中获得唯一的 chrome ID?
- python - [AZURE]OSError:libgomp.so.1:无法打开共享对象文件:没有这样的文件或目录
- python - 获取积分以创建 KDE 图
- sql - SQL 按 10 个最大项分组
- pycharm - 默认情况下防止“使用 Python 控制台运行”
- django - 在 Django 中为一堆复选框创建模型的好方法是什么?
- wordpress - Woocommerce - 如何在订单状态更改时创建 Firebase Firestore 文档
- python - “edit() 接受 1 个位置参数,但给出了 2 个”即使我只给了一个。[不和谐.PY]
- android - 执行应用程序时遇到意外行为,间歇性地出现空检查错误