Google Sheets Script for openByUrl, with a URL from a list (Loop for the URL)

Question

Here is the intent.

I have a Parent sheet (represented by URL www.AAAA.aaa). On it is a tab called "race". I have made 5 copies of this spreadsheet, and the URLs to these copies are listed in a spreadsheet represented by URL www.BBBB.bbb, in a tab called PLAYERS, in the range G2:G20.

I will be making frequent updates to my Parent sheet, in the "race" tab, in the range of L8:L103.

Instead of having to open each of the 5 copies, and pasting the corrections/changes, I am designing this script to:

1. Fetch the values and formulas from www.AAAA.aaa > "race" > L8:L103
2. Fetch the list of URLS from www.BBBB.bbb > "PLAYERS" > G2:G20
3. And paste it over the "race" > L8:L103 in each of the spreadsheets listed in the PLAYERS tab.

(NOTE: There will eventually be hundreds of sheets, but for this example, I only have 5).

In essence, this script will help me "push" corrections to any errors I may have made in the original Parent sheet to each of the copies.

At first, I tried copyTo (to replace the whole sheet, instead of just range L8:L103), but when it deleted the old "race" tab, it broke several data validations that were pointing to the "race" tab. No matter what I tried with copyTo, it always broke something by deleting the old "race" tab.

Here is what I have so far...

function copyValuesAndFormulasBetweenSpreadsheets() {
  // Source information
  const sourceSpreadsheet = SpreadsheetApp.openByUrl("www.AAAA.aaa");
  const sourceSheet = sourceSpreadsheet.getSheetByName("race");
  const sourceRange = sourceSheet.getRange("L8:L103");
  const values = sourceRange.getValues();
  const formulas = sourceRange.getFormulas();
  const urlSpreadsheet = SpreadsheetApp.openByUrl("www.BBBB.bbb");
  const urlSheet = urlSpreadsheet.getSheetByName("PLAYERS");
  var urls = urlSheet.getRange("G2:G20").getValues();

  // Destination information
for (let u = 0; u<urls.length; u++) {
  var destinationSpreadsheet = SpreadsheetApp.openByUrl(urls[u][0]);  //THIS IS WHERE I'M GETTING AN ERROR OF Exception: Invalid argument: url
  const destinationSheet = destinationSpreadsheet.getSheetByName("race")
  const range = destinationSheet.getRange("L8:L103");
  range.setValues(values);
  for (let i = 0; i < formulas.length; i++) {
    for (let j = 0; j < formulas[i].length; j++) {
      if (formulas[i][j]) {
        range.getCell(i+1, j+1).setFormula(formulas[i][j]);
      }
    }
  }
}
}

I am getting the error "Exception: Invalid argument: url" on line 14...

There are two loops (one, that isn't working, is the "Loop through the list of URLs and make the changes). The second loop is to paste both values and formulas. setValues doesn't work right if there are both values and formulas, and setFormulas has the same issue. So, I had to use this solution, as I needed the values to copy over as values, and the formulas to copy over as formulas. This works just fine when I am updating a single, defined URL without a loop.

Answer 1

Figured it out... Script is fine...

After running the console.log that Yuri tested, it pulled the list fine... Except, it was also pulling blank cells...

The range in my script for URLs was G2:G20, however, I currently only had G2:G6 filled with URLs. The error that was being produced was because of the blank cells.

I modified the range to G2:G6, and it ran fine.

Answer 2

Probably you forgot the slash / at the end of these URLs.

It should be:

www.AAAA.aaa/ 
www.BBBB.bbb/ 

or /edit

www.AAAA.aaa/edit
www.BBBB.bbb/edit

or /edit#gid= + some number

www.AAAA.aaa/edit#gid=123
www.BBBB.bbb/edit#gid=456

Just in case. I've tried to recreate the algorithm and it gets no error with my list or URLs:

function open_by_url() {
  var urls = SpreadsheetApp.getActiveSheet().getRange("A1:A3").getValues();
  console.log(urls)
  for (var i in urls) {
    var url = urls[i][0];
    console.log(url);
    var s = SpreadsheetApp.openByUrl(url);
    console.log(s.getName());
  }
}

The list or my URLs looks like this:

The snippet successfully reads the URLs from the list, opens the spreadsheets and prints its names in console.

Actually, for this case I'd probably prefer (... of ...) variant of loop for:

for (let url of urls) { 
  var s = SpreadsheetApp.openByUrl(url);
  // do stuff
}

But it doesn't matter.