Create Amazon US and Canada Links Automatically in Google Sheets

If you work with Amazon KDP, you’ve probably copied URLs like this more times than you’d like to admit:

https://kdp.amazon.com/amazon-dp-action/us/dualbookshelf.marketplacelink/B0G8Y9K2P5

What you really want isn’t that long internal KDP URL — you want clean public Amazon links, like:

• 🇺🇸 https://www.amazon.com/dp/B0G8Y9K2P5
• 🇨🇦 https://www.amazon.ca/dp/B0G8Y9K2P5

Instead of doing this manually every time, we can automate it directly inside Google Sheets using a custom Apps Script function.

No triggers.
No menus.
Just a formula you can drag down a column.

---

🎯 What This Solution Does

• Takes a KDP marketplacelink URL from column B
• Extracts the ASIN
• Outputs:
  • Amazon US link in column C
  • Amazon Canada link in column D
• Works even if the input is:
  • A full KDP URL
  • A standard Amazon /dp/ link
  • Just the ASIN itself

---

🛠 Step 1: Add the Custom Function

1. Open your Google Sheet
2. Go to Extensions → Apps Script
3. Delete any existing code
4. Paste the following:

/**
 * Converts a KDP marketplacelink URL (or ASIN)
 * into Amazon US + Canada links
 *
 * Usage:
 * =AMAZON_LINKS(B2)
 *
 * Output (horizontal):
 * C: https://www.amazon.com/dp/<ASIN>
 * D: https://www.amazon.ca/dp/<ASIN>
 */
function AMAZON_LINKS(input) {
  if (!input) return [["", ""]];

  const text = input.toString().trim();
  let asin = "";

  // Direct ASIN
  const direct = text.match(/\bB0[A-Z0-9]{8}\b/i);
  if (direct) asin = direct[0].toUpperCase();

  // KDP marketplacelink
  if (!asin) {
    const kdp = text.match(/marketplacelink\/([A-Z0-9]{10})/i);
    if (kdp) asin = kdp[1].toUpperCase();
  }

  // Amazon /dp/ fallback
  if (!asin) {
    const dp = text.match(/\/dp\/([A-Z0-9]{10})/i);
    if (dp) asin = dp[1].toUpperCase();
  }

  if (!asin) return [["", ""]];

  // IMPORTANT: return a 2D array for horizontal output
  return [[
    `https://www.amazon.com/dp/${asin}`,
    `https://www.amazon.ca/dp/${asin}`
  ]];
}

Click Save and close the editor.

---

🧪 Step 2: Use the Function in Your Sheet

Assume:

• Column B → KDP URLs or ASINs
• Column C & D → Output links

In C2, enter:

=AMAZON_LINKS(B2)

That’s it.

Google Sheets will automatically “spill” the results:

Column	Result
C	Amazon.com link
D	Amazon.ca link

You can now drag the formula down to process hundreds of books instantly.

---

📌 Example

Input (B2):

https://kdp.amazon.com/amazon-dp-action/us/dualbookshelf.marketplacelink/B0G8Y9K2P5

Output:

C2: https://www.amazon.com/dp/B0G8Y9K2P5
D2: https://www.amazon.ca/dp/B0G8Y9K2P5

---

⚠️ Common Gotcha (and Why This Works)

If you’ve ever seen results appear in C2 and C3 instead of C2 and D2, that’s because:

• Google Sheets treats 1D arrays as vertical
• It treats 2D arrays as horizontal

That’s why this function returns:

[[ usLink, caLink ]]

One row. Two columns.
Exactly what we want.

---

🚀 Easy Extensions

You can expand this function to support more marketplaces:

return [[
  `https://www.amazon.com/dp/${asin}`,
  `https://www.amazon.ca/dp/${asin}`,
  `https://www.amazon.co.uk/dp/${asin}`,
  `https://www.amazon.de/dp/${asin}`
]];

Now it spills cleanly across C → F.

---

🎯 Final Thoughts

This is a great example of how custom Apps Script functions turn Google Sheets into a real publishing workflow tool — especially for:

• KDP authors
• Content marketers
• Affiliate link management
• Multi-region book launches

Once you start thinking this way, Sheets becomes less of a spreadsheet… and more of a lightweight app.

If you want an ARRAYFORMULA version, auto-detection of marketplaces, or a full KDP publishing toolkit in Sheets, just let me know.