Need help handling multiple links in a cell

[umdcurriculum UMD Curriculum]

I am not a developer but I'm trying to troubleshoot a script (pasted below) that is working well, other than one function... 

Basically the script extracts values from the newest row of a spreadsheet (which is populated by form submission), uses those values to replace placeholders in a template document, saves it as a new document, and writes the link for the new document back into a specific cell of the spreadsheet. It also includes some logic about removing blank rows of the table on the generated document, if there's no content in those rows after the values have been imported.

The part that I want to fix is that when one of the spreadsheet cells contains multiple URLs, I want the filenames (and links) for ALL of the URLs to replace my placeholder in the document. Currently, only one filename (from the first URL in the cell) is replacing the placeholder, and it is hyperlinking that filename to the wrong URL from the cell. I need it to treat the URLs separately, replacing the placeholder with multiple filenames (linked to their correct URLs). Is this possible?

As I said, I'm not a developer so I would appreciate if any suggestions can be simplified as much as possible and let me know exactly which lines need to be replaced/modified.

I truly appreciate any help!

function onFormSubmit(e) {

  try {

    Logger.log("Form submitted. Triggered script.");

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

    var lastRow = sheet.getLastRow();

    var values = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];

    Logger.log("Form values: " + values);

    var templateDocId = "1TN0Xi0Xy8GimyrVZBNubUy-7ZNF7FWvF9ECEakYEOqc";

    Logger.log("Template document ID: " + templateDocId);

    var columnGValue = values[6] || "Untitled";

    var columnEValue = values[4] || "NoValue";

    var formattedDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM-dd-yyyy");

    var newFileName = columnEValue + " " + columnGValue + " " + formattedDate;

    Logger.log("New file name: " + newFileName);

    var templateFile = DriveApp.getFileById(templateDocId);

    var newFile = templateFile.makeCopy(newFileName);

    Logger.log("New file created: " + newFile.getName());

    var newDoc = DocumentApp.openById(newFile.getId());

    var body = newDoc.getBody();

    // Replace placeholders within tables

    replacePlaceholdersInTables(body, values);

    replacePlaceholdersWithLinks(body, values);

    // Remove rows where column 1 has content but column 2 is empty

    removeEmptySecondColumnRows(body);

    newDoc.saveAndClose();

    // Write the hyperlink to column E

    var docUrl = newFile.getUrl();

    Logger.log("Writing hyperlink to column E: " + docUrl);

    var columnE = 5; // Column E (1-based index)

    var cell = sheet.getRange(lastRow, columnE);

    var existingText = cell.getValue(); // Get current text in column E

    if (existingText) {

      var richText = SpreadsheetApp.newRichTextValue()

        .setText(existingText) // Keep the existing text

        .setLinkUrl(docUrl)

        .build();

      cell.setRichTextValue(richText);

      Logger.log("Hyperlink successfully added to column E.");

    } else {

      Logger.log("No existing text in column E, hyperlink not applied.");

    }

    // Copy row to another sheet based on column C value

    var columnCValue = values[2]; // Column C (0-based index)

    if (columnCValue) {

      var targetSheet = sheet.getParent().getSheetByName(columnCValue);

      if (targetSheet) {

        var newRow = targetSheet.getLastRow() + 1;

        // Copy raw values first

        targetSheet.appendRow(values);

        // Get hyperlink from column E in the original row

        var sourceCell = sheet.getRange(lastRow, columnE);

        var richTextValue = sourceCell.getRichTextValue();

        if (richTextValue) {

          var targetCell = targetSheet.getRange(newRow, columnE);

          targetCell.setRichTextValue(richTextValue);

          Logger.log("Hyperlink successfully copied to " + columnCValue);

        }

      } else {

        Logger.log("Target sheet not found: " + columnCValue);

      }

    }

  } catch (err) {

    Logger.log("An error occurred: " + err.message);

  }

}

/**

 * Function to replace placeholders within tables

 */

function replacePlaceholdersInTables(body, values) {

  var tables = body.getTables();

  if (tables.length === 0) return;

  tables.forEach(table => {

    var numRows = table.getNumRows();

    for (var i = 0; i < numRows; i++) {

      var numCols = table.getRow(i).getNumCells();

      for (var j = 0; j < numCols; j++) {

        var cell = table.getRow(i).getCell(j);

        var text = cell.getText();

        if (text.trim() !== "") { // Only process non-empty cells

          text = text.replace(/\{\{Proposed effective term\}\}\s*/g, values[6] || "");

          text = text.replace(/\{\{Number\/name\}\}\s*/g, values[4] || "");

          text = text.replace(/\{\{College\/unit\}\}\s*/g, values[2] || "");

          text = text.replace(/\{\{Colleges impacted\}\}\s*/g, values[7] || "");

          text = text.replace(/\{\{Type\}\}\s*/g, values[8] || "");

          text = text.replace(/\{\{New course name\}\}\s*/g, values[9] || "");

          text = text.replace(/\{\{Lib Ed yes\/no\}\}\s*/g, values[12] || "");

          text = text.replace(/\{\{Topic title\}\}\s*/g, values[13] || "");

          text = text.replace(/\{\{Term previously offered\}\}\s*/g, values[14] || "");

          text = text.replace(/\{\{Changes since offering\}\}\s*/g, values[15] || "");

text = text.replace(/\{\{Name to inactivate\}\}\s*/g, values[22] || "");

text = text.replace(/\{\{Program changes needed\}\}\s*/g, values[23] || "");

text = text.replace(/\{\{Name to reactivate\}\}\s*/g, values[24] || "");

text = text.replace(/\{\{Term last active\}\}\s*/g, values[25] || "");

text = text.replace(/\{\{Retain Lib Ed\}\}\s*/g, values[26] || "");

          text = text.replace(/\{\{Other updates\}\}\s*/g, values[28] || "");

text = text.replace(/\{\{Current course name\}\}\s*/g, values[16] || "");

text = text.replace(/\{\{Description changes\}\}\s*/g, values[17] || "");

text = text.replace(/\{\{Summary of changes\}\}\s*/g, values[18] || "");

text = text.replace(/\{\{Rationale\}\}\s*/g, values[19] || "");

text = text.replace(/\{\{Lib Ed yes\/no 2\}\}\s*/g, values[21] || "");

          text = text.replace(/\{\{BOR types of changes\}\}\s*/g, values[31] || "");

          cell.setText(text);

        }

      }

    }

  });

}

/**

 * Function to replace placeholders within tables with hyperlinks

 */

function replacePlaceholdersWithLinks(body, values) {

  var tables = body.getTables();

  if (tables.length === 0) return;

  tables.forEach(table => {

    var numRows = table.getNumRows();

    for (var i = 0; i < numRows; i++) {

      var numCols = table.getRow(i).getNumCells();

      for (var j = 0; j < numCols; j++) {

        var cell = table.getRow(i).getCell(j);

        replaceCellWithLink(cell, "{{New/dual/topic upload}}", values[10] || "");

        replaceCellWithLink(cell, "{{Attachment uploads}}", values[11] || "");

        replaceCellWithLink(cell, "{{New course form for reactivation}}", values[27] || "");

        replaceCellWithLink(cell, "{{Attachment uploads 2}}", values[20] || "");

        replaceCellWithLink(cell, "{{Lib Ed proposals}}", values[33] || "");

        replaceCellWithLink(cell, "{{New program proposal}}", values[29] || "");

        replaceCellWithLink(cell, "{{Upload of program changes}}", values[30] || "");

        replaceCellWithLink(cell, "{{Program discontinuation}}", values[32] || "");

      }

    }

  });

}

/**

 * Function to remove table rows where column 1 has content but column 2 is empty,

 * and also remove rows where both columns are empty.

 */

function removeEmptySecondColumnRows(body) {

  var tables = body.getTables();

  if (tables.length === 0) return;

  tables.forEach(table => {

    var numRows = table.getNumRows();

    // Iterate from bottom to top to avoid index shift issues when deleting

    for (var i = numRows - 1; i >= 0; i--) {

      var row = table.getRow(i);

      var numCells = row.getNumCells();

      if (numCells === 0) continue; // Skip empty rows

      var firstCell = row.getCell(0);

      var firstCellColSpan = firstCell.getColSpan();

      var firstCellText = firstCell.getText().trim();

      if (firstCellColSpan > 1) {

        // The first cell spans multiple columns

        if (firstCellText === "") {

          table.removeRow(i); // Remove row if the merged cell is empty

        }

        // Otherwise, keep the row intact

      } else if (numCells > 1) {

        // The row has at least two separate cells

        var secondCell = row.getCell(1);

        var secondCellText = secondCell.getText().trim();

        if (firstCellText === "" && secondCellText === "") {

          table.removeRow(i); // Remove row if both cells are empty

        } else if (firstCellText !== "" && secondCellText === "") {

          table.removeRow(i); // Remove row if first cell has content but second is empty

        }

        // Otherwise, keep the row intact

      } else {

        // The row has only one cell that doesn't span multiple columns

        if (firstCellText === "") {

          table.removeRow(i); // Remove row if the single cell is empty

        }

        // Otherwise, keep the row intact

      }

    }

  });

  Logger.log("Relevant rows have been processed and removed as necessary.");

}

/**

 * Function to replace a placeholder in a table cell with a hyperlink

 */

function replaceCellWithLink(cell, placeholder, url) {

  var text = cell.getText();

  if (text.includes(placeholder)) {

    if (url && url !== "--") {

      var fileName = url.includes("drive.google.com") ? getFileNameFromUrl(url) : url;

      cell.setText(fileName);

      var link = cell.editAsText();

      link.setLinkUrl(0, fileName.length - 1, url);

    } else {

      cell.setText(""); // Remove placeholder if no valid URL

    }

  }

}

/**

 * Helper function to extract the file name from a Google Drive URL

 */

function getFileNameFromUrl(url) {

  var fileId = extractFileIdFromUrl(url);

  if (fileId) {

    var file = DriveApp.getFileById(fileId);

    return file.getName();

  }

  return "No file";

}

/**

 * Helper function to extract file ID from a Google Drive URL

 */

function extractFileIdFromUrl(url) {

  var match = url.match(/[-\w]{25,}/);

  return match ? match[0] : null;

}

[Brent Guttmann]

I am not really following this part...

Currently, only one filename (from the first URL in the cell) is replacing the placeholder, and it is hyperlinking that filename to the wrong URL from the cell. I need it to treat the URLs separately, replacing the placeholder with multiple filenames (linked to their correct URLs).

It sounds like you may need to do a split on the column that may or may not have multiple urls... So, either add a delimeter, or split by blank space, then for that column, when processing you would loop through how ever many objects there are in the split array and do a replace for each. 

If that's not it then providing an example would clarify what it is you are trying to do.

[umdcurriculum UMD Curriculum]

Thanks for your note. I will try to explain it a bit better... Currently in my spreadsheet, certain cells may contact 1 or more URLs. When there is only one, the filename and hyperlink from that URL is successfully replacing the corresponding placeholder in a new Google Doc. 

When there is more than one URL (separated by a comma and a space) in the spreadsheet cell, the filename from the first URL is replacing the corresponding placeholder in the Google Doc, but the hyperlink actually goes to another URL from the cell. Ideally, I want the placeholder in my document -- for example: {{Attachment uploads}} -- to be replaced with hyperlinked filenames representing each of the URLs -- for example: "Upload #1.doc" and "Upload #2.doc." It doesn't matter whether those filenames end up on separate lines or separated by a comma or space. 

What you mentioned ("loop through how ever many objects there are in the split array and do a replace for each") sounds like it's probably the right thing to do, but I don't know how to do it. I do not know how to write the script and don't know how to proceed.

I hope that's a bit more clear. Thanks!

[Brent Guttmann]

How are you determining which placeholder needs to be replaced? I was assuming each column was for a different placeholder.

.... the filename and hyperlink from that URL is successfully replacing the corresponding placeholder in a new Google Doc.

.... first URL is replacing the corresponding placeholder in the Google Doc

What corresponding placeholder? I get that the placeholder is in the google doc, I need clarification on how you are defining which url goes to which placeholder if there are multiple within a cell.

Do you have an example google sheet you can send? I need to understand what the contents of the cells actually are...

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/NUkaBkaLCYY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/812beda2-523c-46e1-a860-18b5947bdc2bn%40googlegroups.com.

[umdcurriculum UMD Curriculum]

Hello,

Here are my documents:

• Spreadsheet with source data: Curriculum Proposal: Campus Submission Form (Responses) - Google Sheets
• Google Doc template with placeholders: https://docs.google.com/document/d/1TN0Xi0Xy8GimyrVZBNubUy-7ZNF7FWvF9ECEakYEOqc/edit?usp=sharing

A cell like cell K10 in the spreadsheet (which contains 1 URL from a file that has been uploaded via a Google Form), successfully replaces its placeholder {{New dual/topic upload}} in the Google Doc with a filename and hyperlink, as shown in the screenshot below. The script uses "values[10]" to indicate that the value of column K should replace that particular placeholder, and this works fine with only one URL in the source cell.

replaceCellWithLink(cell, "{{New/dual/topic upload}}", values[10] || "");

Cell L10 in the spreadsheet, however, contains two URLs. Values in column L are supposed to replace the placeholder called {{Attachment uploads}} in the Google Doc. The next screenshot shows that one filename does replace the placeholder, but the result I need is both filenames (and hyperlinks), either separated by a comma or on separate lines. There is apparently nothing in the script that is designed to handle multiple URLs.

[As a second bit of troubleshooting, I'd also love to know how to get rid of the text after the hyphen that is getting appended to the filename, but that's a problem for another day.]

I hope this helps. Let me know if I can provide any further detail!

[Brent Guttmann]

Okay, make a copy of this Google Sheet, grab the script and try it out -- See if that's what you want, but I think you should be good to go.

On Tue, Feb 25, 2025 at 11:44 AM 'umdcurriculum UMD Curriculum' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:

Hello,

Here are my documents:

• Spreadsheet with source data: Curriculum Proposal: Campus Submission Form (Responses) - Google Sheets
• Google Doc template with placeholders: https://docs.google.com/document/d/1TN0Xi0Xy8GimyrVZBNubUy-7ZNF7FWvF9ECEakYEOqc/edit?usp=sharing

A cell like cell K10 in the spreadsheet (which contains 1 URL from a file that has been uploaded via a Google Form), successfully replaces its placeholder {{New dual/topic upload}} in the Google Doc with a filename and hyperlink, as shown in the screenshot below. The script uses "values[10]" to indicate that the value of column K should replace that particular placeholder, and this works fine with only one URL in the source cell.

replaceCellWithLink(cell, "{{New/dual/topic upload}}", values[10] || "");

Cell L10 in the spreadsheet, however, contains two URLs. Values in column L are supposed to replace the placeholder called {{Attachment uploads}} in the Google Doc. The next screenshot shows that one filename does replace the placeholder, but the result I need is both filenames (and hyperlinks), either separated by a comma or on separate lines. There is apparently nothing in the script that is designed to handle multiple URLs.

[As a second bit of troubleshooting, I'd also love to know how to get rid of the text after the hyphen that is getting appended to the filename, but that's a problem for another day.]

I hope this helps. Let me know if I can provide any further detail!

On Tuesday, February 25, 2025 at 8:53:54 AM UTC-6 Brent Guttmann wrote:

How are you determining which placeholder needs to be replaced? I was assuming each column was for a different placeholder.

.... the filename and hyperlink from that URL is successfully replacing the corresponding placeholder in a new Google Doc.

.... first URL is replacing the corresponding placeholder in the Google Doc

What corresponding placeholder? I get that the placeholder is in the google doc, I need clarification on how you are defining which url goes to which placeholder if there are multiple within a cell.

Do you have an example google sheet you can send? I need to understand what the contents of the cells actually are...

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/0fea64e0-5bef-400e-bba3-d612871a89ccn%40googlegroups.com.

[umdcurriculum UMD Curriculum]

Sorry, but make a copy of what sheet? If you linked or attached one, I'm afraid I'm not seeing it. Thanks again for taking a look at this.

[Brent Guttmann]

Oh, I think the forum reply emails are converted to text so the link was lost.

Here: https://docs.google.com/spreadsheets/d/1wkEpCrfQapcfDHMUhxEP2gj0eGLdDy9WQUhQ2aJkv2M

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/f6446c08-92af-4053-8c2c-1a52d65fc1b5n%40googlegroups.com.

[umdcurriculum UMD Curriculum]

Fantastic, thank you so much... I've only tested it once so far, but it worked! Greatly appreciated!

[Brent Guttmann]

Good deal. No prob!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/2bc44599-9f0a-4b62-a715-11650eb6f234n%40googlegroups.com.