Bulk URL Shortener In Google sheet using Bitly API & Apps Script

function shortenUrls() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // ✅ Change sheet name if needed
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var urlColumn = 1; // ✅ Column A (1) contains the original URLs
  var shortUrlColumn = 2; // ✅ Column B (2) will store shortened URLs

  // ⚠️ Replace this with your actual Bitly API key (Bearer token)
  var bitlyApiKey = "Bearer YOUR_BITLY_API_KEY";

  for (var i = 0; i < data.length; i++) {
    var url = data[i][urlColumn - 1];

    // ✅ Skip header row & empty cells
    if (i === 0 || !url || typeof url !== 'string' || !url.startsWith("http")) {
      continue;
    }

    try {
      var apiUrl = "https://api-ssl.bitly.com/v4/shorten";
      var headers = {
        "Authorization": bitlyApiKey,
        "Content-Type": "application/json"
      };

      var payload = {
        "long_url": url
      };

      var options = {
        "method": "post",
        "headers": headers,
        "muteHttpExceptions": true,
        "payload": JSON.stringify(payload)
      };

      var response = UrlFetchApp.fetch(apiUrl, options);
      var json = JSON.parse(response.getContentText());

      if (json.link) {
        // ✅ Write shortened URL in Column B
        sheet.getRange(i + 1, shortUrlColumn).setValue(json.link);
      } else {
        // ❌ If API returned an error, show it in the cell
        sheet.getRange(i + 1, shortUrlColumn).setValue("Error: " + (json.message || "Unknown"));
      }

    } catch (error) {
      sheet.getRange(i + 1, shortUrlColumn).setValue("Error: " + error.toString());
    }
  }
}