title: Google Sheets Script for openByUrl, with a URL from a list (Loop for the URL)
body: <p>Here is the intent.</p>
  <p>I have a Parent sheet (represented by URL <a href="http://www.AAAA.aaa" rel="nofollow noreferrer">www.AAAA.aaa</a>).  On it is a tab called &quot;race&quot;.  I have made 5 copies of this spreadsheet, and the URLs to these copies are listed in a spreadsheet represented by URL <a href="http://www.BBBB.bbb" rel="nofollow noreferrer">www.BBBB.bbb</a>, in a tab called PLAYERS, in the range G2:G20.</p>
  <p>I will be making frequent updates to my Parent sheet, in the &quot;race&quot; tab, in the range of L8:L103.</p>
  <p>Instead of having to open each of the 5 copies, and pasting the corrections/changes, I am designing this script to:</p>
  <ol>
  <li>Fetch the values and formulas from <a href="http://www.AAAA.aaa" rel="nofollow noreferrer">www.AAAA.aaa</a> &gt; &quot;race&quot; &gt; L8:L103</li>
  <li>Fetch the list of URLS from <a href="http://www.BBBB.bbb" rel="nofollow noreferrer">www.BBBB.bbb</a> &gt; &quot;PLAYERS&quot; &gt; G2:G20</li>
  <li>And paste it over the &quot;race&quot; &gt; L8:L103 in each of the spreadsheets listed in the PLAYERS tab.</li>
  </ol>
  <p>(NOTE: There will eventually be hundreds of sheets, but for this example, I only have 5).</p>
  <p>In essence, this script will help me &quot;push&quot; corrections to any errors I may have made in the original Parent sheet to each of the copies.</p>
  <p>At first, I tried copyTo (to replace the whole sheet, instead of just range L8:L103), but when it deleted the old &quot;race&quot; tab, it broke several data validations that were pointing to the &quot;race&quot; tab.  No matter what I tried with copyTo, it always broke something by deleting the old &quot;race&quot; tab.</p>
  <p>Here is what I have so far...</p>
  <pre><code>function copyValuesAndFormulasBetweenSpreadsheets() {
    // Source information
    const sourceSpreadsheet = SpreadsheetApp.openByUrl(&quot;www.AAAA.aaa&quot;);
    const sourceSheet = sourceSpreadsheet.getSheetByName(&quot;race&quot;);
    const sourceRange = sourceSheet.getRange(&quot;L8:L103&quot;);
    const values = sourceRange.getValues();
    const formulas = sourceRange.getFormulas();
    const urlSpreadsheet = SpreadsheetApp.openByUrl(&quot;www.BBBB.bbb&quot;);
    const urlSheet = urlSpreadsheet.getSheetByName(&quot;PLAYERS&quot;);
    var urls = urlSheet.getRange(&quot;G2:G20&quot;).getValues();
  
    // Destination information
  for (let u = 0; u&lt;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(&quot;race&quot;)
    const range = destinationSheet.getRange(&quot;L8:L103&quot;);
    range.setValues(values);
    for (let i = 0; i &lt; formulas.length; i++) {
      for (let j = 0; j &lt; formulas[i].length; j++) {
        if (formulas[i][j]) {
          range.getCell(i+1, j+1).setFormula(formulas[i][j]);
        }
      }
    }
  }
  }
  </code></pre>
  <p>I am getting the error &quot;Exception: Invalid argument: url&quot; on line 14...</p>
  <p>There are two loops (one, that isn't working, is the &quot;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.</p>