How to reference a specific list of cells that contain spreadsheet ids, to use in a loop?

0
votes

I have one master sheet that handles a bunch of other spreadsheets, in the sense that the master has a whole bunch of importranges that pull specific info from the child ss's, the child ss's are identical in function, but not in data. Each of the child spreadsheets has their own automated scripts that pertain to each individual ss, but I want any script that affects every ss in an equivalent way to be run from one master sheet. All Child ss's have an identical structure, so the first sheet is named main, which pulls in a bunch of data from the other sheets on that same ss.

Basically, I need to run the exact same function on about 30 spreadsheets, and figured I could make 1 spreadsheet that had one script that would loop through the list of spreadsheets. Preferably, the list of spreadsheet ids is in plain text in a set of cells on the main spreadsheet (Lets say Main!B5:B), so that I could add a new spreadsheet id to the list, and any new spreadsheets that I add would also run the script when I run it.

function myFunction() {
  // This next line is what I'm not sure how to do
    // var ssList = [ Cell B5, Cell B6 ...  ];
     var len = ssList.length 
     var rows = sheet.getDataRange();
     var numRows = rows.getNumRows();
     var values = rows.getValues();
     var rowsDeleted = 0;

  // Spreadsheet loop
     for (var i = 0; i < len; i++) {
         var TargetSheet = SpreadsheetApp.openById(ssList[i]);
     }
}
2
javascriptgoogle-apps-scriptgoogle-sheets
There are many ways to "reference a specific list of cells". Have you already read developers.google.com/apps-script/guides/sheets? By the way, please add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén
The problem is that I don't necessarily know how to word this problem succinctly enough to search for it. I've looked up how to reference a specific list of cells, I've tried going though some codelabs, I've tried a whole bunch of things that really doesn't tell me what I need to know, which is what do I put in the var = ssList line that would actually make the code run. – Chris McMahon
The article that I referred in my previous comment is at introductory level, I see it as something that everyone that want to make a question like this should be read. – Rubén
By the way Is your script bounded to the spreadsheet containing "the specific lists of cells"? or How do you created your Google Apps Script project? – Rubén
I have one master sheet that handles a bunch of other spreadsheets, in the sense that the master has a whole bunch of importranges that pull specific info from the child ss's, the child ss's are identical in function, but not in data. Each of the child spreadsheets has their own automated scripts that pertain to each individual ss, but I want any script that affects every ss in an equivalent way to be run from one master sheet. All Child ss's have an identical structure, so the first sheet is named main, which pulls in a bunch of data from the other sheets on that same ss. – Chris McMahon

2 Answers

2
votes

This should work and you look to be on the right track, but you need to reference the specific column in each row which contains the ID.

getValues() returns a two dimensional array ("array of arrays"), each row is an array of cells. Right now you are passing the entire row to openById, instead of a specific cell.

You don't need to hardcode the list of cells containing IDs into your script, instead just rely on the data that is in your spreadsheet.

For example, if the spreadsheet IDs are in Column A, you need to reference the value at index 0 of the row:

var TargetSheet = SpreadsheetApp.openById(values[i][0]);

Or if the spreadsheet ID's are in Column B, that would be index 1 of the row:

var TargetSheet = SpreadsheetApp.openById(values[i][1]);

To get the value of "len", you can refer to the length of values, the total number of rows in the data range.

var len = values.length;

Fully modified code would look like this:

function myFunction() {
     var rows = sheet.getDataRange();
     var values = rows.getValues();
     var rowsDeleted = 0;

  // Spreadsheet loop
     for (var i = 0; i < values.length; i++) {
         var TargetSheet = SpreadsheetApp.openById(values[i][1]);  //get all values from column "B" (index 1)
     }
}
0
votes
function myFunctionxx() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Main');
  const rg=sh.getRange(5,2,sh.getLastRow()-4,1);
  var list=rg.getValues().map(function(r){return r[0];});//the map flatten a column into 1 dimensional array
  list.forEach(function(ssid){ //the forEach iterates through the list the first parameter is the value of the array,  the second parameter which is not used in this case is the index
    myOtherfunction(ssid);//This is the function that you want to perform on each of the spreadsheets and I'm passing the the spreadsheet id to it.
  });
}

You can read more about the Array map and forEach method here