How to Create Short URLs with Google Sheets 🚀🔗

Google Sheets is a powerful tool for automating URL shortening using APIs like Bitly, TinyURL, and Rebrandly. In this guide, you'll learn:

✅ How to set up a Google Sheets URL shortener
✅ How to use Google Apps Script for automation
✅ How to integrate Bitly, TinyURL, and YOURLS APIs

---

1. Why Use Google Sheets for URL Shortening?

✅ Easy to Use – No coding experience required
✅ Automated – Shortens URLs in bulk
✅ Trackable – Keeps a record of short URLs
✅ API Integration – Works with Bitly, TinyURL, Rebrandly, and YOURLS

---

2. Setting Up Google Sheets for URL Shortening

Step 1: Create a Google Sheet

1. Open Google Sheets (Google Drive)
2. Create a new sheet
3. Name Column A → "Long URL"
4. Name Column B → "Short URL"

---

3. Using Google Apps Script to Shorten URLs

🔹 Method 1: Using Bitly API (Recommended)

Step 1: Get a Bitly API Key

1. Sign up at Bitly Developers
2. Go to API Keys and copy your OAuth Access Token

Step 2: Add Google Apps Script

1. Open Google Sheets
2. Click Extensions > Apps Script
3. Delete existing code and paste this:

function shortenURL() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  var accessToken = "YOUR_BITLY_ACCESS_TOKEN"; // Replace with your Bitly token
  var apiUrl = "https://api-ssl.bitly.com/v4/shorten";

  for (var i = 2; i <= lastRow; i++) {
    var longUrl = sheet.getRange(i, 1).getValue();
    
    if (longUrl && sheet.getRange(i, 2).getValue() === "") { // Check if URL exists & isn't already shortened
      var payload = JSON.stringify({ "long_url": longUrl });
      var options = {
        "method": "POST",
        "headers": {
          "Authorization": "Bearer " + accessToken,
          "Content-Type": "application/json"
        },
        "payload": payload
      };
      
      var response = UrlFetchApp.fetch(apiUrl, options);
      var json = JSON.parse(response.getContentText());
      
      sheet.getRange(i, 2).setValue(json.link); // Save shortened URL
    }
  }
}

Step 3: Run the Script

1. Click the Run ▶ button
2. Grant permissions
3. Your short URLs will appear in Column B

---

🔹 Method 2: Using TinyURL API (No authentication required)

Step 1: Add Google Apps Script

function shortenWithTinyURL() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();

  for (var i = 2; i <= lastRow; i++) {
    var longUrl = sheet.getRange(i, 1).getValue();
    
    if (longUrl && sheet.getRange(i, 2).getValue() === "") { // Avoid overwriting existing URLs
      var apiUrl = "https://api.tinyurl.com/create";
      var payload = JSON.stringify({ "url": longUrl });
      
      var options = {
        "method": "POST",
        "headers": {
          "Authorization": "Bearer YOUR_TINYURL_API_KEY",
          "Content-Type": "application/json"
        },
        "payload": payload
      };

      var response = UrlFetchApp.fetch(apiUrl, options);
      var json = JSON.parse(response.getContentText());
      
      sheet.getRange(i, 2).setValue(json.data.tiny_url);
    }
  }
}

---

🔹 Method 3: Using YOURLS API (Self-hosted URL shortener)

Step 1: Enable YOURLS API

1. Install YOURLS on your domain
2. Enable API in config.php
3. Copy your API signature key

Step 2: Add Google Apps Script

function shortenWithYOURLS() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  var yourlsApiUrl = "https://yourshortdomain.com/yourls-api.php";
  var yourlsSignature = "YOUR_API_SIGNATURE"; // Replace with your signature

  for (var i = 2; i <= lastRow; i++) {
    var longUrl = sheet.getRange(i, 1).getValue();
    
    if (longUrl && sheet.getRange(i, 2).getValue() === "") { 
      var apiUrl = yourlsApiUrl + "?signature=" + yourlsSignature + "&action=shorturl&url=" + encodeURIComponent(longUrl) + "&format=json";
      
      var response = UrlFetchApp.fetch(apiUrl);
      var json = JSON.parse(response.getContentText());

      sheet.getRange(i, 2).setValue(json.shorturl);
    }
  }
}

---

4. Automating URL Shortening in Google Sheets

⏳ Set a Trigger to Run Automatically

1. Go to Extensions > Apps Script
2. Click Triggers (⏰ icon)
3. Click + Add Trigger
4. Choose function shortenURL / shortenWithTinyURL / shortenWithYOURLS
5. Set Time-driven trigger (e.g., every 15 mins)
6. Click Save ✅

Now, every new URL will be automatically shortened! 🎯

---

5. Tracking Clicks & Analytics

🔹 Bitly API: Get Click Stats

function getBitlyClicks() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  var accessToken = "YOUR_BITLY_ACCESS_TOKEN"; // Replace with your token
  
  for (var i = 2; i <= lastRow; i++) {
    var shortUrl = sheet.getRange(i, 2).getValue();
    
    if (shortUrl) {
      var bitlyId = shortUrl.replace("https://bit.ly/", ""); // Extract Bitly ID
      var apiUrl = "https://api-ssl.bitly.com/v4/bitlinks/bit.ly/" + bitlyId + "/clicks";
      
      var options = {
        "method": "GET",
        "headers": { "Authorization": "Bearer " + accessToken }
      };

      var response = UrlFetchApp.fetch(apiUrl, options);
      var json = JSON.parse(response.getContentText());
      
      sheet.getRange(i, 3).setValue(json.total_clicks); // Save click count
    }
  }
}

---

6. Best Practices for Short URL Management

✅ Use a Custom Domain – Makes links look professional
✅ Check API Limits – Avoid hitting free-tier restrictions
✅ Automate with Triggers – Saves time by running daily
✅ Track Analytics – Measure link performance

---

7. Conclusion

🚀 Google Sheets + API integration is the easiest way to create short URLs in bulk! You’ve now learned how to:

✅ Use Google Apps Script to shorten URLs
✅ Automate short URL generation
✅ Track clicks & analytics

Now, try it out and make URL shortening effortless! 🎯

👉 Which method are you using? Let me know in the comments! 😊

Go to Link