Apps Script: How to add google search link to cell value on edit?-CodePudding

My sheet contains 2 columns: A and B. Each cell in column A contains a company name. And each cell in column B contains the formula below to turn each value out of column A into a google search link, where the search term is the value coming from column A.

Here is an example: A3 has the value “Company Test AG”. So, the value in B3 after running the formula below in that very same cell B3 would be https://www.google.de/search?q=Company Test AG

=hyperlink("www.google.de/search?q="&A3,A3)

Question: How can I implement this formula in Apps Script and ensure that it triggers only when the user explicitly writes in column B of the current cell he´s working on i.e. “company details needed”, “company details missing” or something similar?

Thank you so much in advance for your help and hints :)

CodePudding user response：

Issue and solution:

If I understand you correctly, you want the cell in column B to be updated with the hyperlink whenever that same cell is edited with the sentences company details needed or company details missing.

If this is the case, you can do this:

Use an onEdit trigger to execute this on user edits.
Use the event object to check that the appropriate cell and values are written.
Use RichTextValue to build the hyperlink.
Use setRichTextValue to set that hyperlink.

Code sample:

function onEdit(e) {
  addHyperlink(e);
  // Other functions called by onEdit
}

function addHyperlink(e) {
  const SHEET_NAME = "Sheet2"; // Change according to your preferences
  const TEXT_COL_INDEX = 1; // Where text for hyperlink will be written
  const TRIGGER_COL_INDEX = 2; // Text that triggers script (company details, etc.)
  const TARGET_COL_INDEX = 3; // Hyperlink column
  const range = e.range;
  const sheet = range.getSheet();
  const sentences = ["company details needed", "company details missing"].map(s => s.toUpperCase());
  if (range.getColumn() === TRIGGER_COL_INDEX && sheet.getName() === SHEET_NAME && sentences.includes(e.value.toUpperCase())) {
    const row = range.getRow();
    const value = sheet.getRange(row, TEXT_COL_INDEX).getValue(); // Get value in column A
    const link = "www.google.de/search?q="   value;
    const richTextValue = SpreadsheetApp.newRichTextValue()
                                        .setText(value)
                                        .setLinkUrl(link)
                                        .build();
    sheet.getRange(range.getRow(), TARGET_COL_INDEX).setRichTextValue(richTextValue);
  }
}