get ranges inside formula-CodePudding

I would like to know if there is a practical way of extracting the cells that are used in a formula in google scripts? For an example: Let's say A1 has a formula as below

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

I would like var myCellsrecord the data of

page1!C2 page2!B29 page3!C144

Please let me know how would you make this. Thanks in advance

CodePudding user response：

Description

Here is an sample script that can parse equations as shown into the reference cells.

Note this only works for the specific formula you specified.

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);
  }
}

Execution log

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

Reference

CodePudding user response：

Use range.getFormula() to get the formula and then use regex with String.match to get the cells:

/*<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 !)? - [?optional]Matches one or more word followed by ! for sheet name(eg: page1!)
[A-Za-z] \d* - Matches one or more letters [A-Z] followed by zero or more digits \d* for range string(eg: C2)
(:[A-Za-z] \d*)? - [optional] another range string match preceded by a :(eg: :C50)