Hyperlink define problems (dynamic cell references)

[Alistair George]

Hi Ive checked search on hyperlink here and still confused.

Here is my code, which iterates through a list of files and writes a hyperlink to the first cell on each line, leaving the original text in place, so its a real hyperlink to the drive file:

//set up spreadsheet before this line

sheet.appendRow(['                 Play by click on LINK']);

  var file;
  var name;
  var link;
  var row;
  var contents = folder.getFiles();

  while(contents.hasNext()){
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    sheet.appendRow(["temp",name, link]); //"temp" avoids circular reference - its destination for hyperlink
    var value = '=HYPERLINK(C2,B2)';
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange((SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getLastRow()),1).setFormula(value);

    }

===============unsnip========================

The last line

    var value = '=HYPERLINK(C2,B2)'; works for first file entry, but for further iterations/next files, I need to be able to swap out C2,B2 for current cell references eg next line would be C3,B3 eg after each sheet.appendrow()

[Clark Lind]

Just use a simple counter variable. At top:  var counter = 2; Then within the loop at the bottom add counter += (or counter = counter +1).
Then add the counter into your code:
var value = '=HYPERLINK(C' + counter + ',B' + counter + ')';

Something like that should work.

[Clark Lind]

Something like this:

//set up spreadsheet before this line

sheet.appendRow(['                 Play by click on LINK']);

  var file;

  var name;

  var link;

  var row;

  var counter = 2;

  var contents = folder.getFiles();

  while(contents.hasNext()){

    file = contents.next();

    name = file.getName();

    link = file.getUrl();

    sheet.appendRow(["temp",name, link]); //"temp" avoids circular reference - its destination for hyperlink

    var value = '=HYPERLINK(C' + counter + ',B' + counter + ')';

    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange((SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getLastRow()),1).setFormula(value);

    counter = counter +1;

    }

[Alistair George]

You understand whats needed, but the counter variable is outside the scope of hyperlink() this is where I am having the problem, Ive only been using scripts for a few days and Im not sure how to effectively point to a variable outside the scope.

There is another hyperlink thread which gives an answer, but I have had no success in applying it, as their answer was similar to yours (you were on the same thread).

[Clark Lind]

So I understand, you want to iterate through a folder, and for each file, add the filename and corresponding url? 

So you would have:

FileName1
FileName2

etc?

[Alistair George]

Exactly, my code works except for the hyperlink() due to aforementioned problems. Thanks.

[Clark Lind]

I've done something similar with with links to email messages, so the same approach should work. 

This should work:

while(contents.hasNext()){

    file = contents.next();

    name = file.getName();

    link = file.getUrl();

    var hyperlink = '=HYPERLINK(\"' + link + '\",' + '\"' + name + '\")';

    sheet.appendRow([hyperlink]);

    }

[Alistair George]

Hmm, excellent. The link and name are carrying through to hyperlink() thats what I needed.

For the sake of completeness I'll use your method and try to do the same with variable cell references eg after each append using currentrow() as the count.

You seem to have sorted the problem with the use of \"'+link (etc) which is what I couldnt figure. Muchly appreciated.

BTW is there a method on this forum to put [solved] into the topic??

Best wishes, Al.

[Clark Lind]

It took me a while to learn that the quotes were needed inside the hyperlink() function, then had to figure out how to get them in there using 'escapes' ("\").  I don't know about marking threads as solved. 

Good luck!