在公式中获取范围

Question

我想知道是否有一种实用的方法来提取在谷歌脚本公式中使用的单元格?举个例子:假设A1有如下公式

=page1!C2*0,8+page2!B29*0,15+page3!C144*0,05

我想让var myCells记录

的数据page1!C2page2!B29page3!C144

请让我知道你会怎么做。提前感谢

Answer 1

描述

下面是一个示例脚本，可以解析方程，如所示到参考单元格。

注意，这只适用于您指定的特定公式。

Code.gs

function test() {
try {
let spread = SpreadsheetApp.getActiveSpreadsheet();
let sheets = spread.getSheets().map( sheet => sheet.getName() );
// for this test
sheets = ["page1","page2","page3"];
let sheet = spread.getSheetByName("Sheet1");
let formula = sheet.getRange("A1").getFormula();
console.log(formula);
// break into parts
let parts = formula.split("*");  // but notice this is for specific case of *
parts.pop() // the last part doesn't contain any cell reference
console.log(parts);
let i = 0;
let results = [];
parts.forEach( part => {  let j = sheets.findIndex( sheet => part.indexOf(sheet) >= 0 )
// remove sheet from range
let k = part.split('!')[1];  // this give cell A1 notation
results.push(sheets[j]+k)
}
);
console.log(results);
}
catch(err) {
console.log(err);
}
}

执行日志

6:54:44 AM  Notice  Execution started
6:54:46 AM  Info    =page1!C2*0,8+page2!B29*0,15+page3!C144*0,05
6:54:46 AM  Info    [ '=page1!C2', '0,8+page2!B29', '0,15+page3!C144' ]
6:54:46 AM  Info    [ 'page1C2', 'page2B29', 'page3C144' ]
6:54:45 AM  Notice  Execution completed

参考

• Array.map
• Range.getFormula ()
• String.split ()
• Array.pop ()
• Array.forEach ()
• Array.findIndex ()

Answer 2

使用range.getFormula()获取公式，然后使用String.match的regex获取单元格:

/*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/ 
const f =
'=page1!C2:C*0,8+page2!B29*0,15+page3!C144*0,056+sheet1!c:c* Sheet56!D10:D-D5:G10';
const matched = f.match(/(w+!)?[A-Za-z]+d*(:[A-Za-z]+d*)?/g);
console.log(JSON.stringify(matched));

<!-- https://meta.stackoverflow.com/a/375985/ -->    <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>

• (w+!)?- [?可选]匹配一个或多个w字后跟!作为页名(例如:page1!)
• [A-Za-z]+d*-匹配一个或多个字母[A-Z]，后跟0个或多个数字d*，用于范围字符串(例如:C2)
• (:[A-Za-z]+d*)?-[可选]以:开头的另一个范围字符串匹配(例如::C50)