Google Script - iterating through a column of data and then replacing the value of the iteration with a hyperlink at the end

Question

I have a list of product data on a mainSheet in B1:Bn

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.

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.

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.

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
    }
  }
}

I'm sure I'm missing something fairly basic, but I can't work out what that is. Any ideas?

Thanks!

I can see how you are setting the value of "F1" using check01.setValue(urlName); You mean you are unable to set the values of A1, A2 etc corresponding to the products in B1, B2 etc? Can you show a few examples of what you have tried? — bcperth
I think I might have gotten a solution. Above, I was trying to pass various combinations of row[0], data[0] etc as arguments for getRange, ie originCell = getRange(row[0]).setValue(urlName), but it always bombed out at getRange. I've posted below what I've now used to get the result I wanted. — Agent

Agent Agent · Accepted Answer · 2018-10-13T02:25:35

Alright so I finally figured out a way to do this, but I'm not sure if it's the best way. I've set var startRow and I'm using that to define var dataRange.

Then I'm using var iter to count the iterations it works through (at the end)

Then after building my hyperlink function, I'm getting the range where the product name lives again, using mainSheet.getRange(startRow + (iter),2), then setting its value as the var urlName.

function cloneSheetsIter() {
  var ss = SpreadsheetApp.openById("1bF3oqKQtcM31mTLB0asEzntTTQvmiE1e7SUvenMJBi4"); //Get spreadsheet as var
  var mainSheet = ss.getSheetByName("Main");
  var startRow = 1;
  var dataRange = mainSheet.getRange(startRow, 2,2,6);
  var data = dataRange.getValues(); //Get Mainsheet as Var

  var iter = 0; //Used to limit the numberr of sheets this will create
  var maxIter = 2;
  while (iter != maxIter){
    for (i in data){
      var row = data[i];

      var newName = row[0];
      var colour = row[2];

      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 idCell = newSheet.getRange("H2").setValue(colour);

      var urlName = '=HYPERLINK("'+(ss.getUrl())+'#gid='+(ss.getSheetId())+'"'+',"'+newName+'")'; //building an in-cell hyperlink function
      mainSheet.getRange(startRow + (iter),2).setValue(urlName); //The number after (i), is the column where the setValue(Status) will land.

      iter+=1 //Counting number of iterations to check
    }
  }
}

Still, if there are better or more efficient ways to do this, then that would be cool too.

Google Script - iterating through a column of data and then replacing the value of the iteration with a hyperlink at the end

1 Answers