CODEWITHSUNDEEP
google-apps-script
Mr. Dominic
Mr. Dominic

Reputation: 69

how to while loop on this situation

I am looking for a little bit help on this situation. I have a spreadsheet where I create a new sheet, I rename it, and once this is done I do the same for like around 10 other spreadsheets, I would like to know how to implement a for or a while loop so that I dont have to type the repeated code 10 times. My code is below, any help would be really appreciate it. I have the keys in another document, so basically I need to know how to be able to read the keys in a variable and then be able to read each key since they are in a vertical column.

function create(){
var ss0 = SpreadsheetApp.getActiveSpreadsheet();
ss0.setActiveSheet(ss0.getSheetByName("old"));
ss0.setActiveSheet(ss0.duplicateActiveSheet());
ss0.renameActiveSheet("main");
var ss0 = SpreadsheetApp.getActiveSpreadsheet();
ss0.getRange("A4:I500").clearContent();


    //repeated code key 1
var ss0 = SpreadsheetApp.getActiveSheet();
var master = SpreadsheetApp.openById("key1");
ss0.copyTo(master);
var ss0 = SpreadsheetApp.getActiveSpreadsheet();
master.setActiveSheet(master.getSheetByName("Copy of main"));
master.renameActiveSheet("main"); 


    // key 2
var ss0 = SpreadsheetApp.getActiveSheet();
var master = SpreadsheetApp.openById("key2");
ss0.copyTo(master);
var ss0 = SpreadsheetApp.getActiveSpreadsheet();
master.setActiveSheet(master.getSheetByName("Copy of main"));
master.renameActiveSheet("main"); 

}

Upvotes: 0

Views: 1157

Answers (1)

Waqar Ahmad
Waqar Ahmad

Reputation: 3732

You need to get the keys in an array and loop over this array.

Here is an outline

function myFunction() {
  //get the keys in an array
  //say, they are in column A in a spreadsheet from A1:A10
  var keyArray = SpreadsheetApp.openById("ID of the ss with key").getSheetByName("name of the sheet with key")
      .getRange("A1:A10").getValues();

  var ss0 = SpreadsheetApp.getActiveSheet();

  //loop for all the spreadsheets
  for(var i in keyArray){
    var ss = SpreadsheetApp.openById(keyArray[i][0]);
    ss0.copyTo(ss);
    ss.getSheetByName("Copy of main").setName("main")
  }
}

Upvotes: 2

Related Questions