How to Set Values for Discontinuous Cell Locations All at Once?

Question

I am trying to populate a form template on a sheet. Say the a1 locations of the fields on the form to populate are:

[B7, B9, D8, D11]

(In actuality, I have 65 different fields.)

I want to populate the fields with the corresponding set of values:

["Apple", "Banana", "Grape", "Orange"].

(In actuality, the values are coming from a database and will change.)

I have tried a 'for' loop but since I have 65 fields, it is very slow. I saw another post with the same goal, located here, which sets the target form sheet as an array and sets each field one by one to the targeted value. But since I have 65 fields, this would be super cumbersome to write out and very inefficient when I change fields around, etc.

It seems like getRangeList should have a counterpart with some version of setValues to accomplish this, or something like it, but I can't find anything.

There must be a better way! Please help!

---

Here is the 'for' loop I tried, simplified to the shortened list above. My data base has the cell addresses listed above the data, like this:

My code is as follows:

var app = SpreadsheetApp; 
var activeSheet = app.getActiveSpreadsheet(); //Get current active spreadsheet
var dataSheet = activeSheet.getSheetByName('DataSheet');
var formSheet = activeSheet.getSheetByName('FormSheet');

    function Load_Form(){
       formSheet.activate();
       for(i = 2; i<6; i++){
           var dataLOC = dataSheet.getRange(1,i).getValue(); //gets a1 notation of form fields
           var dataRANGE = formSheet.getRange(dataLOC); //activates the cells identified above
           var dataVAL = dataSheet.getRange(2,i).getValue(); // gets values to place in form fields
          

           dataRANGE.setValue(dataVAL);
        }
    }

Keep in mind that I actually have 65 fields, which is why the 'for' loop is so slow.

Answer 1

Well, I never did figure out a way to do this without a 'for' loop. However, thanks to my friend's suggestion, I did look around and figure out how to substantially speed up my for loop.

Here is the revised example code. The code I posted for this example in my original question took me 1,431 ms, whereas the code below took 271 ms. And for my actual database, the original code took 14,314 ms, whereas this revised method took 215 ms. Incredible!

 function Load_Form(){
  formSheet.activate();
  console.time("Time"); //begins timer

  var dataLOC = dataSheet.getRange('B1:E1').getValues(); //Identifies cell addresses on form where we want to paste data
  dataLOC = dataLOC[0]; 
  var dataVAL = dataSheet.getRange('B2:E2').getValues(); // Gets values to paste
  dataVAL = dataVAL[0]; 

   for(i = 0; i<4; i++){
      //Logger.log(dataLOC[i]);
      var dataRANGE = formSheet.getRange(dataLOC[i]); //Activates cell addresses on form to paste data
      dataRANGE.setValue(dataVAL[i]); //sets cell address equal to value we want to paste into it
    }
    console.timeEnd("Time"); // ends timer
}