0

I have a sheets file that requires users to inserts links in the cells of a specific column when they add the information required. However, to make sure this step is done accordingly, I would like to try to force the link to be inserted. The file is this: https://docs.google.com/spreadsheets/d/1iYYuvk5aXIpBmQgwLktqO0enxIBOX6XJk7UXjsdqsqM/edit?usp=sharing

My idea is that, when in a row of column W a user selects one of the options available, a pop-up window or message appears. enter image description here

The prefered way would be to put the link on that pop up and then it would be automatically added in the edited cell. So it would make this: enter image description here

From my experience, if I don't "force" people to insert the link, they will either forget or simply ignore the request. I tried looking for a solution but coudn't find anything.

If this is not possible, a pop up alert message would also help.

I appreciate any help you can give me.

1
  • Please show what you have tried and add a brief description of your search efforts for related content from this site as is suggested in How to Ask. Commented Jan 4, 2023 at 5:05

2 Answers 2

1

Here is a script that will monitor column W and show a dialog box when a value is selected from a drop-down list there.

/**
* Simple trigger that runs each time the user manually edits the spreadsheet.
* Monitors a column and inserts a link when a cell there is edited.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  // version 1.0, written by --Hyde, 10 January 2023
  //  - see https://webapps.stackexchange.com/q/168539/269219
  if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window.');
  const choices = ['PVPR', 'PPAP Serie', 'ECR', 'CPI', 'R&R',];
  if (e.oldValue === e.value
    || !choices.includes(e.value)
    || e.range.columnStart !== 23 // column W
    || e.range.rowStart < 4) {
    return;
  }
  const ss = SpreadsheetApp.getActive();
  const link = prompt_('Please paste in the link to put in this cell:');
  if (!link) {
    ss.toast(`Please select cell ${e.range.getA1Notation()} and insert a link by pressing Control+K.`, 'Link missing', 30);
    return;
  }
  e.range.setFormula(`=hyperlink("${link}"; "${e.value}")`);
}


/**
* Shows a dialog box with a text box and buttons for OK and Cancel.
*
* @param {String} message The message to show.
* @return {String} The text entered in the text box, or null if Cancel was clicked.
*/
function prompt_(message) {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Enter link address', message, ui.ButtonSet.OK_CANCEL);
  return response.getSelectedButton() === ui.Button.OK ? response.getResponseText() : null;
}
2
  • Hello, Thank you for the help. I'll test the link as soon as I can and give feedback. I apologise for any misunderstanding with the original post. Commented Jan 12, 2023 at 10:21
  • Hello doubleunary. Just tested the script and it works. Thank you very much for the help. Commented Jan 23, 2023 at 20:01
0

If scripting isn't required and you just need to validate that the entered data corresponds to a valid link, then why not just use Data Validation with a custom formula as the criteria, and use =ISURL(validation range)?

3
  • Hello. Thank you for your suggestion, but I'm not sure if it would work since the column in question already uses data validation. The person needs to choose one of the four available options. I will change my post, since after your suggestion I understood that I wasn't clear enough. In any case, thank you because I didn't know about the ISURL function and it might be useful in another application. Commented Jan 5, 2023 at 21:40
  • Hmm... the scenario seems to involve rich text links inserted through Control+K or hyperlink() formulas. Neither of those can be detected with isurl(). Commented Jan 10, 2023 at 20:14
  • Agreed - as originally posted it wasn't entirely clear what 'link' meant, and I was working on the assumption that link = url. Commented Jan 10, 2023 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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