0

I have a list of rows in my Google Apps Script stored in a list :

var index = [5.0, 11.0, 20.0, 23.0, 33.0, 40.0, 44.0, 49.0, 52.0]

This list updates everyday and may look like :

var index = [6.0, 11.0, 20.0, 50.0, 56.0, 90.0, 112.0]

etc

I would like these rows to be summed in every column in row 4 :

so N4 would look like =SUM(N5; N11; N20; N23; N33; N40; N44; N49; N52)

O4 =SUM(05; 011; 020; 023; 033; 040; 044; 049; 052)

P4 =SUM(P5; P11; P20; P23; P33; P40; P44; P49; P52) Etc etc until the end of my range.

I have tried the following :

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getDataRange()
var index = []

for (var i = 1; i<range.length; i++){
    if (range[i][0] == "*"){
      index.push(i+1) //this collects all the rows that has * in them
        }
  }

for (var x=0; x<index.length;x++){

   var totalgeneral = sheet.getRange(4,14,1,100)
   var k = index[x]
   var totalgeneral = sheet.getRange(4,14,1,100)
   var formulaArguments = index.map(k => `R[${k}]C[0]`).join()
  //  Logger.log(formulaArguments)
   totalgeneral.setFormulaR1C1("=SUM("+ formulaArguments+"C[0])")
   }

However this gives me the following result : =SUM(R[5]C[0],R[11]C[0],R[20]C[0],R[23]C[0],R[33]C[0],R[40]C[0],R[44]C[0],R[49]C[0],R[52]C[0]C[0])

And it appears as an error in my Google sheet.....

Should I try to go without a R1C1 formula? How can I have the same rows in every column but maintain agility with an updating list?

Many thanks

Julie-Anne
  • 23
  • 6
  • In your script, it seems that `range` is not used, and also, `source` and `opendata` are not declared. But, you say `However this gives me the following result :`. So, I'm worried that you have miscopied your script. How about this? – Tanaike Jan 16 '23 at 11:57
  • @Tanaike I had indeed miscopied my script, mixing an old version with the latest. I've edited it changing `source` and `opendata` . The error remains the same as in my post. – Julie-Anne Jan 16 '23 at 12:58
  • Thank you for replying and updating your question. In your updated script, `var range = sheet.getDataRange()` is Class Range object. By this, after your 1st for loop is finished, `index` is `[]`. By this, the script in your 2nd for loop is not run. But you say `However this gives me the following result :`. So, I'm worried that you have miscopied your script, again. How about this? – Tanaike Jan 16 '23 at 13:13

0 Answers0