title: Google Script - iterating through a column of data and then replacing the value of the iteration with a hyperlink at the end
body: <p>I have a list of product data on a mainSheet in B1:Bn</p>
  
  <p>I've been working on a function to iterate through that product list, and for each product, copy a template sheet, rename that copy with the name of the product, put the product name in the relevant cell in that sheet. That all works fine.</p>
  
  <p>The last thing I want to do is get the URL of that new sheet, and build a function such as =HYPERLINK("https:/[sheet url]","Product Name") and set that function to the origin cell on the main sheet.</p>
  
  <p>Building and setting the function works fine if I point it at a known fixed cell (in this instance F1), but I can't seem to set that function to the origin cell of the product name as it iterates through. I've tried a bunch of different ideas but none of them have worked.</p>
  
  <pre><code>function cloneSheetsIter() {
    var ss = SpreadsheetApp.openById("1bF3oqKQtcM31mTLB0asEzntTTQvmiE1e7SUvenMJBi4"); //Get spreadsheet as var
    var mainSheet = ss.getSheetByName("Main");
    var dataRange = mainSheet.getRange("B1:B2")
    var data = dataRange.getValues(); //Get Mainsheet as Var
  
    var check01 = mainSheet.getRange("F1");  //cells to dump check info into
    var iter = 0; //Used to limit the number of sheets this will create
    var maxIter = 2;
    while (iter != maxIter){
      for (i in data){
        iter+=1 //Counting number of iterations to check
        var newName = data[i];
  
        var newSheet = ss.getSheetByName('Template').copyTo(ss);
        SpreadsheetApp.flush(); // Utilities.sleep(2000);
        newSheet.setName(newName);
        ss.setActiveSheet(newSheet);
        var nameCell = newSheet.getRange("B2").setValue(newName);
  
        var urlName = '=HYPERLINK("'+(ss.getUrl())+'#gid='+(ss.getSheetId())+'"'+',"'+newName+'")'; //building an in-cell hyperlink function
        check01.setValue(urlName); //check01 is a fixed cell just for checking
      }
    }
  }
  </code></pre>
  
  <p>I'm sure I'm missing something fairly basic, but I can't work out what that is. Any ideas?</p>
  
  <p>Thanks!</p>