Shorten Url With Appscript

Description

This Apps Script function allows you to automatically shorten URLs in a Google Sheet using the Bitly API. By setting up an installable trigger, the function will run automatically whenever a long URL is entered in the specified column, making it easier to manage and share shortened URLs.

Instructions

Create a Google Sheet:

Name the sheet (e.g., Sheet1).

Populate the sheet with long URLs in the specified column (e.g., column A).

Get a Bitly Access Token.

Sign up for a Bitly account if you don't have one.

Go to the Bitly API and generate an access token.

Add the Script:

Open your Google Sheet.

Go to Extensions > Apps Script.

Delete any code in the script editor and replace it with the script provided below.

Update the Script:

Replace "Sheet1" with the name of your sheet if different.

Adjust the urlColumn variable to match the column number of the URLs you want to shorten (1-based index).

Replace "YOUR_BITLY_ACCESS_TOKEN" with your Bitly access token.

Set Up an Installable Trigger:

In the Apps Script editor, click on the clock icon to open the triggers page.

Click on + Add Trigger at the bottom right.

Choose shortenUrls function.

Set the event type to On edit.

Save the trigger.

Now, whenever you enter a long URL in the specified column (e.g., column A), the script will automatically shorten the URL without needing to manually run the function.

Script

function shortenUrls() { var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Replace "Sheet1" with your sheet name var dataRange = sheet.getDataRange(); var data = dataRange.getValues(); var urlColumn = 1; // Replace 1 with the column number of the URLs you want to shorten (1-based index) for (var i = 1; i < data.length; i++) { // Start from 1 to skip the header row var url = data[i][urlColumn - 1]; // Adjust for zero-based index if (url) { // Ensure there is a URL to shorten var apiUrl = "https://api-ssl.bitly.com/v4/shorten"; var headers = { "Authorization": "Bearer YOUR_BITLY_ACCESS_TOKEN", // Replace with your Bitly access token "Content-Type": "application/json" }; var payload = { "long_url": url }; var options = { "method": "post", "headers": headers, "payload": JSON.stringify(payload) }; try { var response = UrlFetchApp.fetch(apiUrl, options); var json = JSON.parse(response.getContentText()); var shortenedUrl = json.link; sheet.getRange(i + 1, urlColumn).setValue(shortenedUrl); // Replace the original URL with the shortened URL } catch (e) { Logger.log("Error shortening URL at row " + (i + 1) + ": " + e.message); } } } }

Visit Again

---