Improving Apps Script flexibility by using a column of sheet data instead of hard-coded IDs

Question

Background: My coworkers originally each had a worksheet within the same Google Sheets file that makes a lot of calculations (and was getting unusable). Now, everyone has their own (known) Google Sheets file. To run the same calculations, we need to consolidate all that data into a master sheet (image ref below). We tried =importrange(...), but it's too heavy and breaks often (i.e., Loading... and other unfilled cells).

I've written some code to do this import, but right now its only manual: manually repeating the code and manually add the sheet IDs and changing the destrange.getRange(Cell range) each time. We have 80+ analysts, and fairly high turnover rates, so this would take an absurd amount of time. I'm new to Sheets and Apps Script, and know how to make the script use a cell as reference for a valid range or a valid ID, but I need something that can move a cell down and reference the new info.

Example:
Sheet 1 has a column of everyone Sheet ID
Script Pseudocode

1. get first row's id(Row 1), get sheet tab, get range, copies to active sheet's corresponding row(Row 1).
2. gets second row's id(Row 2), get sheet tab, get range, copies to active sheet's corresponding row (Row 2)
3. etc.

My script understanding is way to low to know how to process this. I have no idea what to read and learn to make it work properly.

function getdata() {
  var confirm = Browser.msgBox('Preparing to draw data','Draw the data like your french girls?', Browser.Buttons.YES_NO);
  if(confirm == 'yes'){
    // I eventually want this to draw the ID from Column A:A, not hard-coded
    var sourcess = SpreadsheetApp.openById('1B9sA5J-Jx0kBLuzP5vZ3LZcSw4CN9sS6A_mSbR9b26g');  
    var sourcesheet = sourcess.getSheetByName('Data Draw');  // source sheet name
    var sourcerange = sourcesheet.getRange('E4:DU4');  // range 
    var sourcevalues = sourcerange.getValues();
    var ss = SpreadsheetApp.getActiveSpreadsheet(); // 
    var destsheet = ss.getSheetByName('Master Totals');  // 
    // This range needs to somehow move one down after each time it pastes a row in.
    var destrange = destsheet.getRange('E4:DU4');
    destrange.setValues(sourcevalues);  // Data into destsheet
  }
}

Any suggestions are greatly appreciated!

Answer 1

Thanks to tehhowch for pointing me in the right direction!

function getdata() {
 var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
 var destsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master Totals');  
 var confirm = Browser.msgBox('Drawing Data','Would you like to update the sheet? It may take 2 to 5 minutes.', Browser.Buttons.YES_NO);
  if(confirm =='yes'){
    var lr = ss.getLastRow();
    for (var i = 4; i<=lr; i++) {
    var currentID = ss.getRange(i, 1).getValue();
   var sourcess = SpreadsheetApp.openByUrl(currentID);  
   var sourcesheet = sourcess.getSheetByName('Data Draw');  
  var sourcerange = sourcesheet.getRange('E4:DU4');  
  var sourcevalues = sourcerange.getValues();
      var destrange = destsheet.getRange('E' +i+':'+ 'DU'+ i);  
  destrange.setValues(sourcevalues);  

I just had to learn how to use a variable loop.

Edit: thanks also to Phil for making my question more presentable!

Answer 2

Now that you've figured out one way to do it, I'll offer an alternative that uses batch methods (i.e. is much more time- and resource-efficient):

function getData() {
  var wb = SpreadsheetApp.getActive();
  var ss = wb.getActiveSheet(); 
  var dest = wb.getSheetByName('Master Totals');
  if (!dest || "yes" !== Browser.msgBox('Drawing Data', 'Would you like to update the sheet? It may take 2 to 5 minutes.',  Browser.Buttons.YES_NO))
    return;

  // Batch-read the first column into an array of arrays of values.
  var ssids = ss.getSheetValues(4, 1, ss.getLastRow() - 4, 1);
  var output = [];
  for (var row = 0; row < ssids.length; ++row) {
    var targetID = ssids[row][0];
    // Open the remote sheet (consider using try-catch
    // and adding error handling).
    var remote = SpreadsheetApp.openById(targetID);
    var source = remote.getSheetByName("Data Draw");
    var toImport =  source.getRange("E4:DU4").getValues();
    // Add this 2D array to the end of our 2D output.
    output = [].concat(output, toImport);
  }
  // Write collected data, if any, anchored from E4.
  if(output.length > 0 && output[0].length > 0)
    dest.getRange(4, 5, output.length, output[0].length).setValues(output);  
}

Each call to getRange and setValues adds measurable time to the execution time - i.e. on the order of hundreds of milliseconds. Minimizing use of the Google interface classes and sticking to JavaScript wherever possible will dramatically improve your scripts' responsiveness.