AppSScript Code

Copy Code

function shortenUrls() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Replace "Sheet1" with the name of your sheet
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var urlColumn = 1; // Replace "1" with the column number of the URLs you want to shorten
  
  for (var i = 0; i < data.length; i++) {
    var url = data[i][urlColumn - 1];
    var apiUrl = "https://api-ssl.bitly.com/v4/shorten";
    var headers = {
      "Authorization": "Your API Key",
      "Content-Type": "application/json"
    };
    var payload = {
      "long_url": url
    };
    var options = {
      "method": "post",
      "headers": headers,
      "payload": JSON.stringify(payload)
    };
    var response = UrlFetchApp.fetch(apiUrl, options);
    var json = JSON.parse(response.getContentText());
    var shortenedUrl = json.link;
    sheet.getRange(i + 1, urlColumn + 1).setValue(shortenedUrl); // Replace "+ 1" with the column number where you want to put the shortened URLs
  }
}