CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
2badatcoding
2badatcoding

Reputation: 115

Google Sheets - Replacing specific parts of formulas in an array

I am trying to write a script to replace the references of some formulas with their current value, before copy to a new range.

I'd like to keep the rest of the formula so I can store a history of values without using new sheets.

I tried this code, but no changes occurred in the formulas:

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

I have this in the formulas array: 

["=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"]

And I want to replace it with this: 

["=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"]

I will be very grateful if anyone can help me.

Upvotes: 2

Views: 135

Answers (1)

Tanaike
Tanaike

Reputation: 201493

  • You want to put the formulas by converting a1Notations to the values.
  • I1 of =eomonth(I1;1) is the date object. You want to keep this and converted the other a1Notations.
  • From your replying, it is Decimals are separated by comma in my country..
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this modification? The flow of this modification is as follows.

Flow:

  1. Retrieve formulas and values from the cells of H1:H6 and C1:H6, respectively.
  2. Retrieve the values of C2, C3, C4, C5, C6, H1.
  3. Convert the retrieved formulas.
    • , is modified to ..
    • The a1Notations of $C$2, $C$3, $C$4, $C$5 and $C$6 are modified to the values from "C2" to "C6".
    • In this case, =eomonth(I1;1) is converted to the current value. So this value can be saved. When sheet.insertColumns(9, 1) is run, I1 of =eomonth(I1;1) is updated to J1. I thought that this can be used.
  4. Insert a column to the column "I".
  5. Put the converted formulas to the cells of I1:I6 which was inserted.

Modified script:

Please modify your script as follows.

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

References:

Upvotes: 1

Related Questions