How to Bulk Create Shortened URLs Using Google Sheets and Google Apps Script

This is the main process that retrieves the URLs from column A of the spreadsheet and outputs the shortened URLs in column B.

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Shortened URLs"); // Specify the sheet name
    if (!sheet) {
        Logger.log("The specified sheet was not found.");
        return;
    }

• The script retrieves the sheet named “Shortened URLs” from the spreadsheet.
• Feel free to change the sheet name as needed.
• However, ensure the sheet name is correct; otherwise, the script will not function properly.

    var apiKey = PropertiesService.getScriptProperties().getProperty('URL_SHORTENER_API_KEY'); // Get API key from script properties
    if (!apiKey) {
        Logger.log("API key is not set.");
        return;
    }

• The script retrieves the API key stored in the Script Properties.
• If the API key is not set, an error message is logged, and the script terminates.

    var lastRow = sheet.getLastRow();
    for (var i = 2; i <= lastRow; i++) { // Start from row 2 to skip headers
        var longUrl = sheet.getRange(i, 1).getValue(); // Column A contains the original URLs
        var existingShortUrl = sheet.getRange(i, 2).getValue(); // Column B stores shortened URLs
        
        // Generate shortened URL only if column B is empty and column A has a value
        if (longUrl && !existingShortUrl) {
            var shortUrl = shortenUrlWithXgd(longUrl, apiKey);
            sheet.getRange(i, 2).setValue(shortUrl); // Write the shortened URL in column B
        }
    }
}

• The script uses a loop (for (var i = 2; i <= lastRow; i++) { ... }) to process each row from the 2nd row to the last row.
• Retrieves the URL from column A (longUrl).
• Checks if a shortened URL already exists in column B (existingShortUrl).
• If a URL is present in column A and column B is empty, it generates a shortened URL.

This function handles the actual URL shortening process using the X.gd API.

    var url = "https://xgd.io/V1/shorten?url=" + encodeURIComponent(longUrl) + "&key=" + encodeURIComponent(apiKey);

• var url = "https://xgd.io/V1/shorten?url=" + encodeURIComponent(longUrl) + "&key=" + encodeURIComponent(apiKey); specifies the endpoint for the X.gd API and appends the long URL and API key to the request.
• UsingencodeURIComponent()ensures the URL and API key are properly encoded, allowing the request to be sent securely.

    var options = {
        method: "GET",
        muteHttpExceptions: true
    };
    
    var response = UrlFetchApp.fetch(url, options);

• The script sends a GET request usingUrlFetchApp.fetch(url, options); .
• ThemuteHttpExceptions: true option allows the script to capture detailed error information in case the request fails.

    if (response.getResponseCode() !== 200) {
        Logger.log("Error: " + response.getContentText());
        throw new Error("Failed to shorten URL. Status code: " + response.getResponseCode());
    }

• The script checks the request’s status code using response.getResponseCode() !== 200.
• If the status code is not 200, an error message is logged to provide details about the failure.

    var data = JSON.parse(response.getContentText());
    Logger.log("Shortened URL: " + data.shorturl); // Log the shortened URL
    return data.shorturl; // Return the shortened URL
}

• The script parses the response content in JSON format and retrieves the shortened URL (data.shorturl).
• This shortened URL is then returned and set in column B of the spreadsheet.

• Batch Processing
  The script automatically shortens multiple URLs entered in column A, significantly reducing manual work.
• Avoiding Duplicate Processing
  Rows with existing shortened URLs in column B are skipped, preventing unnecessary API requests and saving resources.
• Error Handling
  If an API request fails, the script logs an error message, making it easy to identify and resolve issues.