[數據分析#21] Google Sheet 批次產短網址、產生與還原超連結

今天主要分享幾個在 Google Sheet 中處理網址的技巧，其中包括批次處理：縮短網址、還原網址、製作超連結、還原超連結有關的技巧：

1. 如何批次大量產生短連結？
2. 如何批次大量還原短連結？
3. 如何在 Google Sheet 中批次將長網址縮短？
4. 如何在 Google Sheet 中，將儲存格中的文字批次加上超連結？
5. 如何在 Google Sheet 中，批次還原儲存格中的超連結？

如何批次大量產生短連結？

方法一：Google Sheet 外掛

由 Picsee 團隊開發的短連結批次生產工具，可以在 Google Sheet 中安裝，還可以在每個連結中分別埋你需要的 utm_source、utm_medium、utm_campaign 等等資訊

以數據分析的角度，我會比較建議大家用 Google Sheet 跟 Picsee 的外掛工具產生短網址，原因是：日後在做數據分析時可以直接利用同個 Google Sheet 追蹤成效

方法二：網路工具

比較常見的有這兩個：

• reurl.cc (優點：其他人無法還原短網址)
• bit.ly

其他參考資料： Dean 寫得很完整，如果有想使用網路工具，可以參考他的 9 個推薦

如何批次大量還原短連結？

方法一：urlitor.com

• Urlitor 可以一次還原 150 個網址
• 還原後會告訴你網址狀態、網址終點、跳轉網址等等細節
• 還原後可以直接複製到 Google Sheet 中 (版型整齊，可以直接用！)，也可以存成 csv 檔案再用 Google Sheet 或是 Excel 開啟

Google Sheet 實作

1. 如何將長網址縮短

剛剛提到的外掛 =PICSEE_UTM() 就可以批次產出短連結

2. 如何在文字中加入網址

使用 =HYPERLINK()，可以將沒有超連結的文字加上超連結

2. 如何還原儲存格中的超連結

這個要用到指令碼編輯器

1. 工具 > 指令碼編輯器
2. 貼上下方的程式碼 (程式碼中有作者的資訊)
3. 儲存 > 執行 (執行可能會失敗，但失敗後還是可以用) > 接著會叫你驗證 Gmail 帳號
4. 之後回到 Google Sheet 就可以直接使用建立好的函數  =LINKURL()

/** 
 * Returns the URL of a hyperlinked cell, if it's entered with control + k. 
 * Author: @Frederico Schardong based on https://support.google.com/docs/thread/28558721?hl=en&msgid=28927581 and https://github.com/andrebradshaw/utilities/blob/master/google_apps/convertHiddenLinks.gs 
 * Supports ranges
 */
function linkURL(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=w+((.*))/i);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  var formulas = range.getRichTextValues();
  var output = [];
  for (var i = 0; i < formulas.length; i++) {
    var row = [];
    for (var j = 0; j < formulas[0].length; j++) {
      row.push(formulas[i][j].getLinkUrl());
    }
    output.push(row);
  }
  return output
} 

👉 點我查看 Google Sheet 範例

不管是做網站流量數據分析、或是日常在處理一些網址的時候都可以使用！