2

I have multiple links to Google Drive files in an array. I need to get the links into this format in the cell:

File 1, File 2, File 3

The links should be "File 1", "File 2" and "File 3".

The number of links is not the same in each row and I can't figure out is how to dynamically add the correct number of links. The links are coming from Google Form submissions. This is what I have:

var fileList = ["www.link1.com", "www.link2.com", "www.link3.com"];
  var richText = "";
  for (l = 0; l < fileList.length; l++) {
    if (l == 0) {
      var text = "File " + l;
      var newRichText = SpreadsheetApp.newRichTextValue()
        .setText(text)
        .setLinkUrl(0, text.length, fileList[l])
        .build();
    } else {
      var text = ", File " + l;
      var newRichText = SpreadsheetApp.newRichTextValue()
        .setText(text)
        .setLinkUrl(richText.length+2, text.length, fileList[l])
        .build();
    }
    richText = richText + newRichText;

Also tried for loops in the middle of the RichTextValueBuilder thing and there's also a copy() thing but neither worked for me. Those bits of code were gone hours ago so I can't post them here.

I'm not really that good at JS so it's possible I'm making a basic mistake.

2

I figured out the answer. Hopefully it helps someone else.

var fileList = ["www.file1.com", "www.file2.com", "www.file3.com"];
var richText = SpreadsheetApp.newRichTextValue();
var text = "";
var links = [];

for (i = 0; i < fileList.length; i++) {
  if (i == 0) { // Sets the start of the link.
    var start = text.length;
  } else {
    var start = text.length + 2;
  }
  if (i == 0) { // Sets the file number.
    text = "File 1"
  } else {
    var num = i + 1;
    text = text + ", File " + num.toString();
  }
  var end = text.length; // Sets the end of the link.
  links.push([start, end, fileList[i]]);
}

richText.setText(text);
for (l = 0; l < links.length; l++) {
  richText.setLinkUrl(links[l][0],links[l][1],links[l][2])
}

richText = richText.build();
1
  • Thank you for posting this when you figured it out, you have helped me as well! (several years later haha) Apr 25 '21 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.