Google Apps Script – Dynamically Add Multiple URLs into Rich Text in Google Sheets

google sheetsgoogle-apps-script

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.

Best Answer

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();
Related Topic