You can easily integrate Generation of tracker urls into Google Sheets via custom Javascript function. For Excel integration, click here.
Step 1: Open the Spreadsheet and navigate to Tools -> Script Editor
Step 2: Add Function Definition for Tracker Integration
/**
* Tradable Bits Tracker
* @customfunction
*/
function createTracker(name, group, url, network, tracker_key) {
// tracker_name, tracker_group_name and tracker_url are required
// network and tracker_key are optional
var tracker_name = name;
var tracker_group_name = group;
var tracker_url = url;
var network = network;
var tracker_key = tracker_key;
if (!(tracker_name && tracker_group_name && tracker_url)) {
return null
}
// TradableBits Public API Key goes here
var api_key = 'XXXX-XXXX-XXXXX-XXXXX';
var data = {
'api_key': api_key,
'tracker_name': tracker_name,
'tracker_group_name': tracker_group_name,
'tracker_url': tracker_url,
'tracker_key': tracker_key,
'network': network,
'virtual_domain': 'tradablebits.com'
}
var url = 'https://tradablebits.com/api/v1/trackers/';
var options = {
'method': 'post',
'payload': data
}
var response = JSON.parse(UrlFetchApp.fetch(url, options).getContentText())
return 'https://' + response.virtual_domain + '/trk/' + response.tracker_key
}
function onOpen() {
// Checkbox column location
var checkboxColumn = "F"
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange(checkboxColumn + ":" + checkboxColumn);
var values = range.getValues();
for (var i = 0; i < values.length; i++) {
if((values[i][0] == true))
values[i][0] = false;
}
range.setValues(values);
}
Step 3: Open the Spreadsheet and make cell F1 a checkbox
Step 4: Enter the value in cell G1 as
=IF(F1=TRUE, createTracker(A1,B1,C1,D1,E1), G1)
Step 5: Navigate to File -> Settings -> Calculation tab -> Turn on Iterative calculation. Set Max number of iterations to 1
Step 6: Assuming that values in A1,B1,C1,D1,E1 are correct, and you put the correct API KEY, tracker shortened url will show up in cell G1 after F1 is checked
Step 1: On the Home tab, select the Add-ins button and import Script Lab.
Step 2: Create a new Script Snippet and paste the following code
/**
* Tradable Bits Tracker
* @customfunction
*/
async function createTracker(tracker_name, tracker_group_name, tracker_url, marketing_group, network, tracker_key) {
// tracker_name, tracker_group_name and tracker_url are required
// tracker_marketing_group_name, network and tracker_key are optional
if (!(tracker_name && tracker_group_name && tracker_url)) {
return null;
}
// TradableBits Public API Key goes here
var api_key = 'XXXX-XXXX-XXXXX-XXXXX';
const apiUrl = "https://tradablebits.com/api/v1/trackers";
var data = {
api_key: api_key,
tracker_name: tracker_name,
tracker_group_name: tracker_group_name,
tracker_url: tracker_url,
tracker_marketing_group_name: marketing_group,
tracker_key: tracker_key,
network: network,
virtual_domain: "tradablebits.com"
};
var formData = new FormData();
Object.entries(data).forEach(([key, value]) => {
if (value != null) {
formData.append(key, value);
}
});
try {
const res = await fetch(apiUrl, {
method: "POST",
body: formData
});
if (res.ok) {
const responseJson = await res.json();
return "https://" + responseJson.virtual_domain + "/trk/" + responseJson.tracker_key;
} else {
return await res.text();
}
} catch (error) {
return error;
}
}
Step 3: Name the script TBITS and then click Register
Step 4: Make cell G1 a checkbox
Step 5: Enter the value in cell H1 as
=IF(G1=TRUE, SCRIPTLAB.TBITS.CREATETRACKER(A1,B1,C1,D1,E1,F1), H1)
Step 6: Assuming that values in A1,B1,C1,D1,E1,F1 are correct, you put the correct API KEY, and the script is named TBITS, tracker shortened url will show up in cell H1 after G1 is checked
NOTE: Script Lab stores the script locally on the browser so each user would need to import it. It is recommended to use Google Sheets if possible because the script is stored on the cloud.