如何在Google Sheets中输入亚马逊订单号自动生成超链接

Got it, so you want to type an Amazon UK order number (like 123-1234567-1234567) directly into a cell, and have that cell instantly turn into a clickable hyperlink to the order details page—no need to rely on a separate cell with CONCAT. Here's how to make that happen in Google Sheets:

This method will automatically convert any valid Amazon order number you type into a cell into the hyperlinked version you want. It’s the closest to your request of having the input cell itself handle the conversion.

Step-by-Step Setup #

1. Open your Google Sheet, go to Extensions > Apps Script to launch the script editor.
2. Delete the default myFunction() code, then paste the following script:

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  const inputValue = range.getValue().toString().trim();
  
  // Regex to match Amazon's standard order number format: XXX-XXXXXXX-XXXXXXX
  const orderNumberRegex = /^\d{3}-\d{7}-\d{7}$/;
  
  if (orderNumberRegex.test(inputValue)) {
    const baseUrl = "https://www.amazon.co.uk/gp/your-account/order-details/ref=ppx_yo_dt_b_order_details_o00?ie=UTF8&orderID=";
    const fullUrl = baseUrl + inputValue;
    
    // Replace the raw order number with the HYPERLINK formula
    range.setFormula(`=HYPERLINK("${fullUrl}", "${inputValue}")`);
  }
}

3. Click the save icon (💾) and name the project something like AmazonOrderLinker.
4. Close the script editor and return to your sheet. The next time you type a valid order number (matching the 3-7-7 digit-with-hyphens format) into any cell, it will automatically convert to a clickable hyperlink.

Quick Notes: #

• The regex ensures only valid order number formats get converted—tweak the pattern if you need to support different order number lengths.
• First-time authorization: When the script runs for the first time, you’ll need to grant it permissions. Follow the prompts, and click "Advanced" > "Go to [Project Name]" to proceed (this is normal for custom scripts).

If you prefer a non-automated approach where you explicitly call a function (instead of auto-conversion), create a custom function:

1. Go to Extensions > Apps Script and add this function:

function AMAZONLINK(orderNumber) {
  const baseUrl = "https://www.amazon.co.uk/gp/your-account/order-details/ref=ppx_yo_dt_b_order_details_o00?ie=UTF8&orderID=";
  const fullUrl = baseUrl + orderNumber.toString().trim();
  return `=HYPERLINK("${fullUrl}", "${orderNumber}")`;
}

2. Save the project, then in your sheet, type =AMAZONLINK("123-1234567-1234567") (or reference a cell with the order number: =AMAZONLINK(A1)). This outputs the hyperlink formula, which you can use directly.

Why Not a Built-In Formula Alone? #

Google Sheets doesn’t have a native way to automatically convert typed text into a formula in the same cell without Apps Script. Built-in functions like HYPERLINK require you to enter the formula explicitly, not just the raw order number. The script approach fills that gap by listening for edits and updating the cell automatically.

内容的提问来源于stack exchange，提问作者Luke C