I am looking to enter Amazon order numbers into a Google Sheet such as the following 123-1234567-1234567

What I then want is for the cell to automatically become a hyper link with the following format:

https://www.amazon.co.uk/gp/your-account/order-details/ref=ppx_yo_dt_b_order_details_o00?ie=UTF8&orderID=ORDER_NUMBER_HERE

I am aware I could enter the tracking number into a cell, and then have the cell next to it show this link using CONCAT, However I am looking for the cell that I enter the ornder number into, to automatically change itself to a hyperlinked version (with the https://www... prepended)

For example, I click on an empty cell. I enter 123-1234567-1234567. The cell then automatically becomes =hyperlink("https://www.amazon.co.uk/gp/your-account/order-details/ref=ppx_yo_dt_b_order_details_o00?ie=UTF8&orderID=123-1234567-1234567", "123-1234567-1234567")

Thanks in advance.

CodePudding user response：

Use an onEdit(e) simple trigger, like this:

/**
* Simple trigger that runs each time the user manually edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
        'It runs automatically when you hand edit the spreadsheet. '
        'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  autoAsinHyperlink_(e);
}


/**
* Monitors values that are manually entered in the spreadsheet and
* inserts a hyperlink formula when the format matches asinRegex.
*
* @param {Object} e The onEdit() event object.
*/
function autoAsinHyperlink_(e) {
  // version 1.0, written by --Hyde, 11 January 2023
  //  - see https://stackoverflow.com/q/75086109/13045193
  const asinRegex = /^(\d[-\d]{8,}\d)$/;
  if (!e.value || !e.value.trim().match(asinRegex)) {
    return;
  }
  const link = 'https://www.amazon.co.uk/gp/your-account/order-details/ref=ppx_yo_dt_b_order_details_o00?ie=UTF8&orderID=';
  e.range.setFormula(`=hyperlink("${link}${e.value}"; "${e.value}")`);
}