如何用 Google Sheets 效率產生 Bitly 短網址

大家好，我是 Eric。

身為行銷人員，往往會有大量建立短網址的需求，而這些短網址存在的目的，是為了要協助客戶建立帶有 UTM 參數的連結，用以在 Google Analytics 或其他追蹤工具追蹤網站流量的成效。其中 Bitly 就是其中一種常見的短網址產生工具。

但是每次都要進 Bitly 建立網址，實在是一件很麻煩的事，加上希望集中管理短網址的參數，因此我研究了應該如何透過 Bitly 的 API，實現用 Google Sheets 管理並建立 Bitly 短網址的功能。

要實現這個功能，會分成 3 個步驟進行：Google Sheets、Google Apps Scripts 跟 Bitly，這篇文章附上測試過的程式碼，如果你也是有類似需求的行銷人員，這篇文章會相當適合你。

Google sheets 的架構

這份 Google Sheets 是我們公司內部使用的架構，欄位 B 到 E 的地方可以根據自己的需求做調整、新增，欄位 A，以及最後的 3 個欄位 (Complete URL、Go Short 與 Short URL) 建議不要更動。欄位 A 的資料會用於拼接完整網址時 utm_content 的參考，最後的 3 個欄位則與 Google Apps Scripts 的程式有關。

觀察 Complete URL，這個欄位的主要工作是把前面代表 utm 參數的欄位值通通拼湊起來。

IF(ISTEXT(G2),CONCATENATE(G2,"?",IF(ISTEXT(H2),"utm_source="&H2,""),IF(ISTEXT(I2),"&utm_medium="&I2,""),IF(ISTEXT(J2),"&utm_campaign="&J2,""),IF(ISTEXT(K2),"&utm_term="&K2,""),"&utm_content=",IF(ISTEXT(L2),L2,A2)),"")

取得 Bitly 的 API 權杖

由於 Bitly 舊版的 API 已於 2023 年年初淘汰不用，因此沒有辦法單純用公式就取得短網址。新的 API 存取方式，必須透過 POST 要求，從 Bitly 的伺服器產生短網址。為此，我們必須先前往 Bitly 產生 API 權杖。步驟如下：

1. 登入後在左側選單選擇 [Settings]。
2. 選擇 [Developer settings] 的 [API]。
3. 輸入你的密碼。
4. 按下 [Generate token]。
5. 將 [Access Token] 記下後，存放在安全的地方。

Google Apps Scripts

接著，你必須從你的 Google Sheets 建立對應的 Google Apps Scripts。首先選取選單的 [擴充功能] > [Apps Scripts]。

專案設定

進入 Apps Scripts 的介面後，點選左邊的 [專案設定 (Project Settings)]，並勾選 [在編輯器中顯示「appsscript.json」資訊清單檔案]。這個設定會留到稍後做說明。

接著到下方 [指令碼屬性 (Script Properties)] 中點選 [編輯指令碼屬性 (Edit script properties)]，在 [屬性 (Property)] 欄位填入 BITLY_CREDENTIAL，屬性的 [值 (Value)] 則貼上剛才複製的 API 權杖，並儲存。

接著回到編輯器，會發現多了一個 appsscript.json 檔案，點擊編輯後，用下列的設定取代原本的內容，其中最重要的是 "oauthScopes": ["https://www.googleapis.com/auth/script.external_request"]這一段，因為我們接下來需要傳送外部要求：

{
  "timeZone": "Asia/Taipei",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Sheets",
        "version": "v4",
        "serviceId": "sheets"
      }
    ]
  },
  "webapp": {
    "executeAs": "USER_ACCESSING",
    "access": "ANYONE"
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

編輯完成後，你可以先儲存專案，再進行下一步。

主要程式碼

打開你的預設程式碼檔案，刪除掉 function myFunction() {} 這整段程式碼，並貼上下列程式碼，儲存專案：

var scriptProperties = PropertiesService.getScriptProperties();
var bitlyCred = scriptProperties.getProperty('BITLY_CREDENTIAL');

function editSetup(e) {
  if (!e || !e.range) {
    return;
  }
  var sheet = e.range.getSheet();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; // Get the header row
  var urlIndex = headers.indexOf("Complete URL") + 1; // Get the index of the "Complete URL" column
  var shortIndex = headers.indexOf("Go Short") + 1; // Get the index of the "Go Short" column
  Logger.log("Sheet name: " + sheet.getName()); // Log the sheet name
  if (col == shortIndex && e.value == "TRUE" && row != 1) { // If the value in the "Go Short" column is "TRUE" and it's not in the header row
    Logger.log("Calling shortenUrl() function");
    shortenUrl(sheet, row, urlIndex, shortIndex);
  }
}

function shortenUrl(sheet, row, urlIndex, shortIndex) {
  Logger.log("Sheet name in shortenUrl() function: " + sheet.getName());
  var longUrl = sheet.getRange(row, urlIndex).getValue(); // Get the long URL from the "Complete URL" column
  Logger.log("Long URL is: " + longUrl );
  var data = {
    "domain": "bit.ly",
    "long_url": longUrl
  };
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(data),
    'headers': {
      'Authorization': 'Bearer ' + bitlyCred,
    },
    'muteHttpExceptions': true
  };
  var response = UrlFetchApp.fetch('https://api-ssl.bitly.com/v4/shorten', options);
  Logger.log("The raw response: " + response );
  var result = JSON.parse(response.getContentText());
  Logger.log("The short url result: " + result.link );
  sheet.getRange(row, shortIndex + 1).setValue(result.link); // Set the shortened URL in the "Go Short" column
}

接下來，為了針對必要的權限進行授權，先點擊上方的 [執行 (Run)] 按鈕，並同意全部需要的權限。

設定觸發條件 (Trigger)

為了讓程式在我們點擊 Go Short 的時候會自動觸發，取得短網址，我們需要另外設定觸發條件。

首先，新增觸發條件，在 [執行的功能 (Choose which function to run)] 選擇 editSetup()，[選取活動類型 (Select event type)] 則選擇 [編輯文件時 (On Edit)]。

儲存後系統可能會再要求一次授權。

接著再回到 Google Sheets，按照欄位填入對應資料並產生完整網址後，勾選 [Go Short]，就會產生短網址了。

補充說明

其實 Google Apps Scripts 預設可以透過 function onEdit(e){} 的方式觸發，但我在測試時，會一直發生權限問題，因此才會改用這種做法。

另外，用這種 API 產生短網址，與透過 Bitly 介面產生短網址的方式是相同的，因此也會受到產生短網址數量的限制。我的 Bitly 帳號是在 2017 年建立的，當時每個月可以免費產生 10,000 組短網址，但是新的免費帳號只能每個月產生 10 組短網址，因此如果你很有可能會需要評估你的使用頻率。

最後，這篇文章中的程式碼是我透過 ChatGPT 輔助完成的 (80%)，整個核心的程式碼只花了 30 分鐘就可以運作，但是我在後來的權限問題上花了不少時間試錯，才改用手動觸發條件的設定方式。這更進一步顯示，只要你問對問題，並且有能力測試答案的正確性，那 ChatGPT 真的可以大幅降低程式撰寫的門檻。