Simplify an Apps Script code for Google Sheet for multiple .getRange().setValue()

0
votes

Is it possible to improve with this kind of code which sets values in multiple cells?

 var depart = ligne.filter(String).length;
   if ( depart < 27 ) {
      var partence = depart + 2
   } else {
      var partence = depart + 13
   }

  commande.getRange(partence, 2).setValue(designation);
  commande.getRange(partence, 15).setValue(designation);
  commande.getRange(partence, 26).setValue(designation);
  commande.getRange(partence, 37).setValue(designation);
  commande.getRange(partence, 49).setValue(designation);
  commande.getRange(partence, 61).setValue(designation);
  commande.getRange(partence, 73).setValue(designation);
  commande.getRange(partence, 85).setValue(designation);
  commande.getRange(partence, 97).setValue(designation);
  commande.getRange(partence, 109).setValue(designation);

It is possible to do something like this ? :

commande.getRange(partence, 2 And 15 And 26 And 37 And 49 And 61 And 73 And 85 And 97 And 109).setValue(designation);

2
google-apps-scriptgoogle-sheets
When naming variables, please note that it is good practice to choose names that make sense in English to help others understand your code. Besides, partence is not a word in any language, even French. Maybe partance is what you meant? In any case, combining languages in code can only make it harder to read. - Antoine Colson
Ok, Thank you. I'll pay attention next time. - Arnaud

2 Answers

1
votes

A range in Apps Script is always a rectangular part of a sheet. If you have to modify the values in several cells that are not adjacent to each other, they are separate ranges and you have to do a lot of getRange setValue calls just as you did. There is nothing like getRange(partence, 2 And 15).

But if your row "partence" does not contain any formulas, you can work with a single range that contains all cells that need to be set. The other values in that range will be read and then set back unchanged (this would however remove any formulas in those cells): 

var ix = [2, 15, 26, 37, 49, 61, 73, 85, 97, 109];
var range = commande.getRange(partence, ix[0], 1, ix[ix.length-1] - ix[0] + 1);
var values = range.getValues();
for (i in ix) {
  values[0][ix[i] - ix[0]] = designation;
}
range.setValues(values);
0
votes

You can try something like this : First make an array of the values that you want to insert/set in the sheet and then in single-shot insert that array.

var depart = ligne.filter(String).length;
   if ( depart < 27 ) {
      var partence = depart + 2
   } else {
      var partence = depart + 13
   }
var values = [];
var values = commande.getRange(partence, 1, 110, 1).getValues();
var i;
for (i = 1; i <= values.length; i++) {
    if(i == 2 || i == 15 || i == 26 || i == 37 || i == 49 || i == 61 || i == 73 || i == 85 || i == 97 || i == 109) {
    values[i-1][0] = designation;
}
}

commande.getRange(partence, 1, 110, 1).setValues(values);

This is an un-tested code. Let me know if there is some bugs, I'll be happy to help us to sort out.

Thanks