Skip to content

Instantly share code, notes, and snippets.

@cyberdev

cyberdev/code.gs

Created January 21, 2021 08:28
Show Gist options
  • Save cyberdev/4b41e30ad2c236e5648e18e274beb1be to your computer and use it in GitHub Desktop.
Save cyberdev/4b41e30ad2c236e5648e18e274beb1be to your computer and use it in GitHub Desktop.
Download ZIP
Load data from API and Display it on Google Sheets
Raw
code.gs
/*********************************************************************************************************************************************
* Get data from url
**********************************************************************************************************************************************/
function getData(url){
var options = {
'contentType' : 'application/json',
'headers' : {
'Authorization' : '<token>'
}
};
var response = UrlFetchApp.fetch(url, options);
var status = response.getResponseCode();
var result = JSON.parse(response.getContentText())
if(status==200){
var result = {
status: 'success',
result: result
};
}else{
var result = {
status: 'failure',
message: result.message
};
}
return result;
}
/*********************************************************************************************************************************************
* Get data from url
**********************************************************************************************************************************************/
function getAllData(urls){
var requests = new Array();
var i=0;
urls.forEach(function(url){
requests[i] = {
'url' : url,
'method' : 'get',
'contentType' : 'application/json',
'headers' : {
'Authorization' : '<token>'
}
};
i++;
});
var responses = UrlFetchApp.fetchAll(requests);
return responses;
}
/*********************************************************************************************************************************************
* get first empty row
**********************************************************************************************************************************************/
function getFirstEmptyRow(sheet, currentRow) {
var result = null;
while (result == null) {
var cellValue = sheet.getRange(currentRow, 1).getValue();
if (cellValue == "") {
result = currentRow;
} else {
currentRow++;
}
}
return result;
}
/*********************************************************************************************************************************************
* Custom Report
**********************************************************************************************************************************************/
/*function loadCustomReport(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
var rowStart = 6;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
//Logger.log(rowStart + ' - ' + rowEnd);
var columnIndexes = {
"customReportId": 1,
"insertedDate": 2,
"month": 3,
"name": 4,
"url": 5,
"year": 6
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["customReportId"]).setValue('');
sheet.getRange(row, columnIndexes["insertedDate"]).setValue('');
sheet.getRange(row, columnIndexes["month"]).setValue('');
sheet.getRange(row, columnIndexes["name"]).setValue('');
sheet.getRange(row, columnIndexes["url"]).setValue('');
sheet.getRange(row, columnIndexes["year"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/customreports';
var response = getData(url);
if(response.status=='success'){
var total = response.result.length;
if(total > 0){
response.result.forEach(function(data) {
sheet.getRange(rowStart, columnIndexes["customReportId"]).setValue(data["customReportId"]);
sheet.getRange(rowStart, columnIndexes["insertedDate"]).setValue(data["insertedDate"]);
sheet.getRange(rowStart, columnIndexes["month"]).setValue(data["month"]);
sheet.getRange(rowStart, columnIndexes["name"]).setValue(data["name"]);
sheet.getRange(rowStart, columnIndexes["url"]).setValue(data["url"]);
sheet.getRange(rowStart, columnIndexes["year"]).setValue(data["year"]);
rowStart++;
});
}else{
Browser.msgBox("No result to display");
}
}else{
Browser.msgBox("Error: " + response.message);
}
}*/
/*********************************************************************************************************************************************
* Keyword Rankings
**********************************************************************************************************************************************/
function loadKeywordRanking(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
var rowStart = 7;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
var columnIndexes = {
//"areaAreaId": 1,
//"areaInsertedDate": 2,
//"areaName": 3,
//"areaId": 4,
"averageRank": 1,
"currentPage": 2,
"firstPageDate": 3,
"firstPageUrl": 4,
//"insertedDate": 9,
//"isAreaFirst": 10,
//"isTracking": 11,
//"keywordInsertedDate": 12,
//"keywordKeywordId": 13,
"keywordName": 5,
"keywordArea": 6,
//"keywordId": 15,
"rank": 7,
"startPage": 8,
"startRank": 9
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
//sheet.getRange(row, columnIndexes["areaAreaId"]).setValue('');
//sheet.getRange(row, columnIndexes["areaInsertedDate"]).setValue('');
//sheet.getRange(row, columnIndexes["areaName"]).setValue('');
//sheet.getRange(row, columnIndexes["areaId"]).setValue('');
sheet.getRange(row, columnIndexes["averageRank"]).setValue('');
sheet.getRange(row, columnIndexes["currentPage"]).setValue('');
sheet.getRange(row, columnIndexes["firstPageDate"]).setValue('');
sheet.getRange(row, columnIndexes["firstPageUrl"]).setValue('');
//sheet.getRange(row, columnIndexes["insertedDate"]).setValue('');
//sheet.getRange(row, columnIndexes["isAreaFirst"]).setValue('');
//sheet.getRange(row, columnIndexes["isTracking"]).setValue('');
//sheet.getRange(row, columnIndexes["keywordInsertedDate"]).setValue('');
//sheet.getRange(row, columnIndexes["keywordKeywordId"]).setValue('');
sheet.getRange(row, columnIndexes["keywordName"]).setValue('');
sheet.getRange(row, columnIndexes["keywordArea"]).setValue('');
//sheet.getRange(row, columnIndexes["keywordId"]).setValue('');
sheet.getRange(row, columnIndexes["rank"]).setValue('');
sheet.getRange(row, columnIndexes["startPage"]).setValue('');
sheet.getRange(row, columnIndexes["startRank"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/keywordrankings';
var response = getData(url);
if(response.status=='success'){
var total = response.result.length;
if(total > 0){
response.result.forEach(function(data) {
//sheet.getRange(rowStart, columnIndexes["areaAreaId"]).setValue(data["area"]['areaId']);
//sheet.getRange(rowStart, columnIndexes["areaInsertedDate"]).setValue(data["area"]['insertedDate']);
//sheet.getRange(rowStart, columnIndexes["areaName"]).setValue(data["area"]['name']);
//sheet.getRange(rowStart, columnIndexes["areaId"]).setValue(data['areaId']);
sheet.getRange(rowStart, columnIndexes["averageRank"]).setValue(data['averageRank']);
sheet.getRange(rowStart, columnIndexes["currentPage"]).setValue(data['currentPage']);
sheet.getRange(rowStart, columnIndexes["firstPageDate"]).setValue(data['firstPageDate']);
sheet.getRange(rowStart, columnIndexes["firstPageUrl"]).setValue(data['firstPageUrl']);
//sheet.getRange(rowStart, columnIndexes["insertedDate"]).setValue(data['insertedDate']);
//sheet.getRange(rowStart, columnIndexes["isAreaFirst"]).setValue(data['isAreaFirst']);
//sheet.getRange(rowStart, columnIndexes["isTracking"]).setValue(data['isTracking']);
//sheet.getRange(rowStart, columnIndexes["keywordInsertedDate"]).setValue(data['keyword']['insertedDate']);
//sheet.getRange(rowStart, columnIndexes["keywordKeywordId"]).setValue(data['keyword']['keywordId']);
sheet.getRange(rowStart, columnIndexes["keywordName"]).setValue(data['keyword']['name']);
sheet.getRange(rowStart, columnIndexes["keywordArea"]).setValue(data["area"]['name']);
//sheet.getRange(rowStart, columnIndexes["keywordId"]).setValue(data['keywordId']);
sheet.getRange(rowStart, columnIndexes["rank"]).setValue(data['rank']);
sheet.getRange(rowStart, columnIndexes["startPage"]).setValue(data['startPage']);
sheet.getRange(rowStart, columnIndexes["startRank"]).setValue(data['startRank']);
rowStart++;
});
}else{
Browser.msgBox("No result to display");
}
}else{
Browser.msgBox("Error: " + response.message);
}
}
/*********************************************************************************************************************************************
* Keyword Rankings History
**********************************************************************************************************************************************/
function loadKeywordRankingHistory(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
//var keywordid = sheet.getRange(2, 2).getValue();
//var areaid = sheet.getRange(3, 2).getValue();
//var isareafirst = sheet.getRange(4, 2).getValue();
var rowStart = 7;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
var columnIndexes = {
"areaAreaId": 1,
"areaInsertedDate": 2,
"areaName": 3,
"areaId": 4,
"averageRank": 5,
"currentPage": 6,
"firstPageDate": 7,
"firstPageUrl": 8,
"insertedDate": 9,
"isAreaFirst": 10,
"isTracking": 11,
"keywordInsertedDate": 12,
"keywordKeywordId": 13,
"keywordName": 14,
"keywordId": 15,
"rank": 16,
"startPage": 17,
"startRank": 18
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["areaAreaId"]).setValue('');
sheet.getRange(row, columnIndexes["areaInsertedDate"]).setValue('');
sheet.getRange(row, columnIndexes["areaName"]).setValue('');
sheet.getRange(row, columnIndexes["areaId"]).setValue('');
sheet.getRange(row, columnIndexes["averageRank"]).setValue('');
sheet.getRange(row, columnIndexes["currentPage"]).setValue('');
sheet.getRange(row, columnIndexes["firstPageDate"]).setValue('');
sheet.getRange(row, columnIndexes["firstPageUrl"]).setValue('');
sheet.getRange(row, columnIndexes["insertedDate"]).setValue('');
sheet.getRange(row, columnIndexes["isAreaFirst"]).setValue('');
sheet.getRange(row, columnIndexes["isTracking"]).setValue('');
sheet.getRange(row, columnIndexes["keywordInsertedDate"]).setValue('');
sheet.getRange(row, columnIndexes["keywordKeywordId"]).setValue('');
sheet.getRange(row, columnIndexes["keywordName"]).setValue('');
sheet.getRange(row, columnIndexes["keywordId"]).setValue('');
sheet.getRange(row, columnIndexes["rank"]).setValue('');
sheet.getRange(row, columnIndexes["startPage"]).setValue('');
sheet.getRange(row, columnIndexes["startRank"]).setValue('');
}
}
//get all keyword ranking
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/keywordrankings';
var response = getData(url);
if(response.status=='success'){
var total = response.result.length;
if(total > 0){
//loop result
var i = 0;
var urls = new Array();
response.result.forEach(function(data) {
var keywordid = data['keywordId'];
var areaid = data['areaId'];
var isareafirst = data['isAreaFirst'];
//call every result
urls[i] = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/keywordrankings/history?keywordid=' + keywordid + '&areaid=' + areaid + '&isareafirst=' + isareafirst;
i++;
});
var responses = getAllData(urls);
responses.forEach(function(rowresp) {
var status = rowresp.getResponseCode();
var records = JSON.parse(rowresp.getContentText())
if(status==200){
records.forEach(function(data_detail){
sheet.getRange(rowStart, columnIndexes["areaAreaId"]).setValue(data_detail["area"]['areaId']);
sheet.getRange(rowStart, columnIndexes["areaInsertedDate"]).setValue(data_detail["area"]['insertedDate']);
sheet.getRange(rowStart, columnIndexes["areaName"]).setValue(data_detail["area"]['name']);
sheet.getRange(rowStart, columnIndexes["areaId"]).setValue(data_detail['areaId']);
sheet.getRange(rowStart, columnIndexes["averageRank"]).setValue(data_detail['averageRank']);
sheet.getRange(rowStart, columnIndexes["currentPage"]).setValue(data_detail['currentPage']);
sheet.getRange(rowStart, columnIndexes["firstPageDate"]).setValue(data_detail['firstPageDate']);
sheet.getRange(rowStart, columnIndexes["firstPageUrl"]).setValue(data_detail['firstPageUrl']);
sheet.getRange(rowStart, columnIndexes["insertedDate"]).setValue(data_detail['insertedDate']);
sheet.getRange(rowStart, columnIndexes["isAreaFirst"]).setValue(data_detail['isAreaFirst']);
sheet.getRange(rowStart, columnIndexes["isTracking"]).setValue(data_detail['isTracking']);
sheet.getRange(rowStart, columnIndexes["keywordInsertedDate"]).setValue(data_detail['keyword']['insertedDate']);
sheet.getRange(rowStart, columnIndexes["keywordKeywordId"]).setValue(data_detail['keyword']['keywordId']);
sheet.getRange(rowStart, columnIndexes["keywordName"]).setValue(data_detail['keyword']['name']);
sheet.getRange(rowStart, columnIndexes["keywordId"]).setValue(data_detail['keywordId']);
sheet.getRange(rowStart, columnIndexes["rank"]).setValue(data_detail['rank']);
sheet.getRange(rowStart, columnIndexes["startPage"]).setValue(data_detail['startPage']);
sheet.getRange(rowStart, columnIndexes["startRank"]).setValue(data_detail['startRank']);
rowStart++;
});
}else{
Logger.log(rowresp.message);
}
});
}else{
Browser.msgBox("No result to display");
}
}else{
Browser.msgBox("Error: " + response.message);
}
/*var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/keywordrankings/history?keywordid=' + keywordid + '&areaid=' + areaid + '&isareafirst=' + isareafirst;
var response = getData(url);
if(response.status=='success'){
var total = response.result.length;
if(total > 0){
response.result.forEach(function(data) {
sheet.getRange(rowStart, columnIndexes["areaAreaId"]).setValue(data["area"]['areaId']);
sheet.getRange(rowStart, columnIndexes["areaInsertedDate"]).setValue(data["area"]['insertedDate']);
sheet.getRange(rowStart, columnIndexes["areaName"]).setValue(data["area"]['name']);
sheet.getRange(rowStart, columnIndexes["areaId"]).setValue(data['areaId']);
sheet.getRange(rowStart, columnIndexes["averageRank"]).setValue(data['averageRank']);
sheet.getRange(rowStart, columnIndexes["currentPage"]).setValue(data['currentPage']);
sheet.getRange(rowStart, columnIndexes["firstPageDate"]).setValue(data['firstPageDate']);
sheet.getRange(rowStart, columnIndexes["firstPageUrl"]).setValue(data['firstPageUrl']);
sheet.getRange(rowStart, columnIndexes["insertedDate"]).setValue(data['insertedDate']);
sheet.getRange(rowStart, columnIndexes["isAreaFirst"]).setValue(data['isAreaFirst']);
sheet.getRange(rowStart, columnIndexes["isTracking"]).setValue(data['isTracking']);
sheet.getRange(rowStart, columnIndexes["keywordInsertedDate"]).setValue(data['keyword']['insertedDate']);
sheet.getRange(rowStart, columnIndexes["keywordKeywordId"]).setValue(data['keyword']['keywordId']);
sheet.getRange(rowStart, columnIndexes["keywordName"]).setValue(data['keyword']['name']);
sheet.getRange(rowStart, columnIndexes["keywordId"]).setValue(data['keywordId']);
sheet.getRange(rowStart, columnIndexes["rank"]).setValue(data['rank']);
sheet.getRange(rowStart, columnIndexes["startPage"]).setValue(data['startPage']);
sheet.getRange(rowStart, columnIndexes["startRank"]).setValue(data['startRank']);
rowStart++;
});
}else{
Browser.msgBox("No result to display");
}
}else{
Browser.msgBox("Error: " + response.message);
}*/
}
/*********************************************************************************************************************************************
* Keyword Rankings Weekly Analitics
**********************************************************************************************************************************************/
function loadKeywordRankingWeeklyAnalitics(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
var rowStart = 6;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
//Logger.log(rowStart + ' - ' + rowEnd);
var columnIndexes = {
"avgRank": 1,
"avgRankChangePercentage": 2,
"currentRank": 3,
"keywordsOnFirstPage": 4,
"keywordsOnFirstPageChangePercentage": 5,
"topRankingKeyword": 6
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["avgRank"]).setValue('');
sheet.getRange(row, columnIndexes["avgRankChangePercentage"]).setValue('');
sheet.getRange(row, columnIndexes["currentRank"]).setValue('');
sheet.getRange(row, columnIndexes["keywordsOnFirstPage"]).setValue('');
sheet.getRange(row, columnIndexes["keywordsOnFirstPageChangePercentage"]).setValue('');
sheet.getRange(row, columnIndexes["topRankingKeyword"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/keywordrankings/analytics/week/current';
var response = getData(url);
if(response.status=='success'){
var data = response.result;
sheet.getRange(rowStart, columnIndexes["avgRank"]).setValue(data["avgRank"]);
sheet.getRange(rowStart, columnIndexes["avgRankChangePercentage"]).setValue(data["avgRankChangePercentage"]);
sheet.getRange(rowStart, columnIndexes["currentRank"]).setValue(data["currentRank"]);
sheet.getRange(rowStart, columnIndexes["keywordsOnFirstPage"]).setValue(data["keywordsOnFirstPage"]);
sheet.getRange(rowStart, columnIndexes["keywordsOnFirstPageChangePercentage"]).setValue(data["keywordsOnFirstPageChangePercentage"]);
sheet.getRange(rowStart, columnIndexes["topRankingKeyword"]).setValue(data["topRankingKeyword"]);
}else{
Browser.msgBox("Error: " + response.message);
}
}
/*********************************************************************************************************************************************
* Keywords And Areas
**********************************************************************************************************************************************/
/*function loadKeywordsAndAreas(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
var rowStart = 7;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
var columnIndexes = {
"address": 1,
"areas": 2,
"description": 3,
"keywords": 4,
"title": 5,
"verticalName": 6,
"verticalVerticalId": 7
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["address"]).setValue('');
sheet.getRange(row, columnIndexes["areas"]).setValue('');
sheet.getRange(row, columnIndexes["description"]).setValue('');
sheet.getRange(row, columnIndexes["keywords"]).setValue('');
sheet.getRange(row, columnIndexes["title"]).setValue('');
sheet.getRange(row, columnIndexes["verticalName"]).setValue('');
sheet.getRange(row, columnIndexes["verticalVerticalId"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/keywordsandareas';
var response = getData(url);
if(response.status=='success'){
var data = response.result;
sheet.getRange(rowStart, columnIndexes["address"]).setValue(data["address"]);
var realRowArea = rowStart;
data["areas"].forEach(function(area) {
sheet.getRange(realRowArea, columnIndexes["areas"]).setValue(area);
realRowArea++;
});
sheet.getRange(rowStart, columnIndexes["description"]).setValue(data["description"]);
var realRowKeyword = rowStart;
data["keywords"].forEach(function(keyword) {
sheet.getRange(realRowKeyword, columnIndexes["keywords"]).setValue(keyword);
realRowKeyword++;
});
sheet.getRange(rowStart, columnIndexes["title"]).setValue(data["title"]);
sheet.getRange(rowStart, columnIndexes["verticalName"]).setValue(data["vertical"]["name"]);
sheet.getRange(rowStart, columnIndexes["verticalVerticalId"]).setValue(data["vertical"]["verticalId"]);
}else{
Browser.msgBox("Error: " + response.message);
}
}*/
/*********************************************************************************************************************************************
* Local Profiles
**********************************************************************************************************************************************/
function loadLocalProfiles(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
var rowStart = 7;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
var columnIndexes = {
"localProfileId": 1,
"customerId": 2,
"insertedDate": 3,
"localProfileCouponsCode": 4,
"localProfileCouponsBeginDate": 5,
"localProfileCouponsDescription": 6,
"localProfileCouponsDetails": 7,
"localProfileCouponsEndDate": 8,
"localProfileCouponsImageUrl": 9,
"localProfileCouponsLocalProfileCouponId": 10,
"localProfileCouponsLocalProfileId": 11,
"localProfileCouponsTitle": 12,
"localProfileSchedulesLocalProfileScheduleId": 13,
"localProfileSchedulesLocalProfileId": 14,
"localProfileSchedulesDay": 15,
"localProfileSchedulesOpenTimeString": 16,
"localProfileSchedulesCloseTimeString": 17,
"companyName": 18,
"streetAddress": 19,
"city": 20,
"state": 21,
"zip": 22,
"phone": 23,
"email": 24,
"website": 25,
"description": 26,
"category1": 27,
"category2": 28,
"category3": 29,
"category4": 30,
"category5": 31,
"googleProfileUrl": 32,
"contactName": 33,
"notes": 34,
"registrationEmail": 35,
"registrationPassword": 36,
"hasServiceArea": 37,
"primaryServiceLocation": 38,
"areaServed1": 39,
"areaServed2": 40,
"areaServed3": 41,
"areaServed4": 42,
"showOperatingHours": 43,
"isOpen247": 44,
"isByAppointmentOnly": 45,
"acceptsCash": 46,
"acceptsCheck": 47,
"acceptsTravelersCheck": 48,
"acceptsInvoice": 49,
"acceptsAmericanExpress": 50,
"acceptsDinersClub": 51,
"acceptsDiscover": 52,
"acceptsMasterCard": 53,
"acceptsVisa": 54,
"acceptsFinancing": 55,
"acceptsGoogleCheckout": 56,
"acceptsPaypal": 57,
"hasPrimaryServiceLocation": 58,
"primaryServiceLocationRadius": 59,
"isListingClaimedByClient": 60,
"isListingOptimized": 61,
"logoUrl": 62,
"imageUrls": 63,
"videoUrls": 64,
"isStreetAddressHidden": 65,
"isListingVerified": 66,
"isListingUnclaimed": 67,
"countryId": 68,
"isBingPinVerified": 69,
"isGooglePinVerified": 70,
"isYahooPinVerified": 71
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["localProfileId"]).setValue('');
sheet.getRange(row, columnIndexes["customerId"]).setValue('');
sheet.getRange(row, columnIndexes["insertedDate"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileCouponsCode"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileCouponsBeginDate"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileCouponsDescription"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileCouponsDetails"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileCouponsEndDate"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileCouponsImageUrl"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileCouponsLocalProfileCouponId"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileCouponsLocalProfileId"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileCouponsTitle"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileSchedulesLocalProfileScheduleId"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileSchedulesLocalProfileId"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileSchedulesDay"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileSchedulesOpenTimeString"]).setValue('');
sheet.getRange(row, columnIndexes["localProfileSchedulesCloseTimeString"]).setValue('');
sheet.getRange(row, columnIndexes["companyName"]).setValue('');
sheet.getRange(row, columnIndexes["streetAddress"]).setValue('');
sheet.getRange(row, columnIndexes["city"]).setValue('');
sheet.getRange(row, columnIndexes["state"]).setValue('');
sheet.getRange(row, columnIndexes["zip"]).setValue('');
sheet.getRange(row, columnIndexes["phone"]).setValue('');
sheet.getRange(row, columnIndexes["email"]).setValue('');
sheet.getRange(row, columnIndexes["website"]).setValue('');
sheet.getRange(row, columnIndexes["description"]).setValue('');
sheet.getRange(row, columnIndexes["category1"]).setValue('');
sheet.getRange(row, columnIndexes["category2"]).setValue('');
sheet.getRange(row, columnIndexes["category3"]).setValue('');
sheet.getRange(row, columnIndexes["category4"]).setValue('');
sheet.getRange(row, columnIndexes["category5"]).setValue('');
sheet.getRange(row, columnIndexes["googleProfileUrl"]).setValue('');
sheet.getRange(row, columnIndexes["contactName"]).setValue('');
sheet.getRange(row, columnIndexes["notes"]).setValue('');
sheet.getRange(row, columnIndexes["registrationEmail"]).setValue('');
sheet.getRange(row, columnIndexes["registrationPassword"]).setValue('');
sheet.getRange(row, columnIndexes["hasServiceArea"]).setValue('');
sheet.getRange(row, columnIndexes["primaryServiceLocation"]).setValue('');
sheet.getRange(row, columnIndexes["areaServed1"]).setValue('');
sheet.getRange(row, columnIndexes["areaServed2"]).setValue('');
sheet.getRange(row, columnIndexes["areaServed3"]).setValue('');
sheet.getRange(row, columnIndexes["areaServed4"]).setValue('');
sheet.getRange(row, columnIndexes["showOperatingHours"]).setValue('');
sheet.getRange(row, columnIndexes["isOpen247"]).setValue('');
sheet.getRange(row, columnIndexes["isByAppointmentOnly"]).setValue('');
sheet.getRange(row, columnIndexes["acceptsCash"]).setValue('');
sheet.getRange(row, columnIndexes["acceptsCheck"]).setValue('');
sheet.getRange(row, columnIndexes["acceptsTravelersCheck"]).setValue('');
sheet.getRange(row, columnIndexes["acceptsInvoice"]).setValue('');
sheet.getRange(row, columnIndexes["acceptsAmericanExpress"]).setValue('');
sheet.getRange(row, columnIndexes["acceptsDinersClub"]).setValue('');
sheet.getRange(row, columnIndexes["acceptsDiscover"]).setValue('');
sheet.getRange(row, columnIndexes["acceptsMasterCard"]).setValue('');
sheet.getRange(row, columnIndexes["acceptsVisa"]).setValue('');
sheet.getRange(row, columnIndexes["acceptsFinancing"]).setValue('');
sheet.getRange(row, columnIndexes["acceptsGoogleCheckout"]).setValue('');
sheet.getRange(row, columnIndexes["acceptsPaypal"]).setValue('');
sheet.getRange(row, columnIndexes["hasPrimaryServiceLocation"]).setValue('');
sheet.getRange(row, columnIndexes["primaryServiceLocationRadius"]).setValue('');
sheet.getRange(row, columnIndexes["isListingClaimedByClient"]).setValue('');
sheet.getRange(row, columnIndexes["isListingOptimized"]).setValue('');
sheet.getRange(row, columnIndexes["logoUrl"]).setValue('');
sheet.getRange(row, columnIndexes["imageUrls"]).setValue('');
sheet.getRange(row, columnIndexes["videoUrls"]).setValue('');
sheet.getRange(row, columnIndexes["isStreetAddressHidden"]).setValue('');
sheet.getRange(row, columnIndexes["isListingVerified"]).setValue('');
sheet.getRange(row, columnIndexes["isListingUnclaimed"]).setValue('');
sheet.getRange(row, columnIndexes["countryId"]).setValue('');
sheet.getRange(row, columnIndexes["isBingPinVerified"]).setValue('');
sheet.getRange(row, columnIndexes["isGooglePinVerified"]).setValue('');
sheet.getRange(row, columnIndexes["isYahooPinVerified"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/localprofiles';
var response = getData(url);
if(response.status=='success'){
var total = response.result.length;
if(total > 0){
response.result.forEach(function(data) {
sheet.getRange(rowStart, columnIndexes["localProfileId"]).setValue(data["localProfileId"]);
sheet.getRange(rowStart, columnIndexes["customerId"]).setValue(data["customerId"]);
sheet.getRange(rowStart, columnIndexes["insertedDate"]).setValue(data["insertedDate"]);
if(data["localProfileCoupons"]!=null){
var rowCoupons = rowStart;
data["localProfileCoupons"].forEach(function(rcd) {
sheet.getRange(rowCoupons, columnIndexes["localProfileCouponsCode"]).setValue(rcd['code']);
sheet.getRange(rowCoupons, columnIndexes["localProfileCouponsBeginDate"]).setValue(rcd['beginDate']);
sheet.getRange(rowCoupons, columnIndexes["localProfileCouponsDescription"]).setValue(rcd['description']);
sheet.getRange(rowCoupons, columnIndexes["localProfileCouponsDetails"]).setValue(rcd["details"]);
sheet.getRange(rowCoupons, columnIndexes["localProfileCouponsEndDate"]).setValue(rcd["endDate"]);
sheet.getRange(rowCoupons, columnIndexes["localProfileCouponsImageUrl"]).setValue(rcd["imageUrl"]);
sheet.getRange(rowCoupons, columnIndexes["localProfileCouponsLocalProfileCouponId"]).setValue(rcd["localProfileCouponId"]);
sheet.getRange(rowCoupons, columnIndexes["localProfileCouponsLocalProfileId"]).setValue(rcd["localProfileId"]);
sheet.getRange(rowCoupons, columnIndexes["localProfileCouponsTitle"]).setValue(rcd["title"]);
rowCoupons++
})
}
if(data["localProfileSchedules"]!=null){
var rowSchedulle = rowStart;
data["localProfileSchedules"].forEach(function(rcd) {
sheet.getRange(rowSchedulle, columnIndexes["localProfileSchedulesLocalProfileScheduleId"]).setValue(rcd["localProfileScheduleId"]);
sheet.getRange(rowSchedulle, columnIndexes["localProfileSchedulesLocalProfileId"]).setValue(rcd["localProfileId"]);
sheet.getRange(rowSchedulle, columnIndexes["localProfileSchedulesDay"]).setValue(rcd["day"]);
sheet.getRange(rowSchedulle, columnIndexes["localProfileSchedulesOpenTimeString"]).setValue(rcd["openTimeString"]);
sheet.getRange(rowSchedulle, columnIndexes["localProfileSchedulesCloseTimeString"]).setValue(rcd["closeTimeString"]);
rowSchedulle++;
});
}
sheet.getRange(rowStart, columnIndexes["companyName"]).setValue(data["companyName"]);
sheet.getRange(rowStart, columnIndexes["streetAddress"]).setValue(data["streetAddress"]);
sheet.getRange(rowStart, columnIndexes["city"]).setValue(data["city"]);
sheet.getRange(rowStart, columnIndexes["state"]).setValue(data["state"]);
sheet.getRange(rowStart, columnIndexes["zip"]).setValue(data["zip"]);
sheet.getRange(rowStart, columnIndexes["phone"]).setValue(data["phone"]);
sheet.getRange(rowStart, columnIndexes["email"]).setValue(data["email"]);
sheet.getRange(rowStart, columnIndexes["website"]).setValue(data["website"]);
sheet.getRange(rowStart, columnIndexes["description"]).setValue(data["description"]);
sheet.getRange(rowStart, columnIndexes["category1"]).setValue(data["category1"]);
sheet.getRange(rowStart, columnIndexes["category2"]).setValue(data["category2"]);
sheet.getRange(rowStart, columnIndexes["category3"]).setValue(data["category3"]);
sheet.getRange(rowStart, columnIndexes["category4"]).setValue(data["category4"]);
sheet.getRange(rowStart, columnIndexes["category5"]).setValue(data["category5"]);
sheet.getRange(rowStart, columnIndexes["googleProfileUrl"]).setValue(data["googleProfileUrl"]);
sheet.getRange(rowStart, columnIndexes["contactName"]).setValue(data["contactName"]);
sheet.getRange(rowStart, columnIndexes["notes"]).setValue(data["notes"]);
sheet.getRange(rowStart, columnIndexes["registrationEmail"]).setValue(data["registrationEmail"]);
sheet.getRange(rowStart, columnIndexes["registrationPassword"]).setValue(data["registrationPassword"]);
sheet.getRange(rowStart, columnIndexes["hasServiceArea"]).setValue(data["hasServiceArea"]);
sheet.getRange(rowStart, columnIndexes["primaryServiceLocation"]).setValue(data["primaryServiceLocation"]);
sheet.getRange(rowStart, columnIndexes["areaServed1"]).setValue(data["areaServed1"]);
sheet.getRange(rowStart, columnIndexes["areaServed2"]).setValue(data["areaServed2"]);
sheet.getRange(rowStart, columnIndexes["areaServed3"]).setValue(data["areaServed3"]);
sheet.getRange(rowStart, columnIndexes["areaServed4"]).setValue(data["areaServed4"]);
sheet.getRange(rowStart, columnIndexes["showOperatingHours"]).setValue(data["showOperatingHours"]);
sheet.getRange(rowStart, columnIndexes["isOpen247"]).setValue(data["isOpen247"]);
sheet.getRange(rowStart, columnIndexes["isByAppointmentOnly"]).setValue(data["isByAppointmentOnly"]);
sheet.getRange(rowStart, columnIndexes["acceptsCash"]).setValue(data["acceptsCash"]);
sheet.getRange(rowStart, columnIndexes["acceptsCheck"]).setValue(data["acceptsCheck"]);
sheet.getRange(rowStart, columnIndexes["acceptsTravelersCheck"]).setValue(data["acceptsTravelersCheck"]);
sheet.getRange(rowStart, columnIndexes["acceptsInvoice"]).setValue(data["acceptsInvoice"]);
sheet.getRange(rowStart, columnIndexes["acceptsAmericanExpress"]).setValue(data["acceptsAmericanExpress"]);
sheet.getRange(rowStart, columnIndexes["acceptsDinersClub"]).setValue(data["acceptsDinersClub"]);
sheet.getRange(rowStart, columnIndexes["acceptsDiscover"]).setValue(data["acceptsDiscover"]);
sheet.getRange(rowStart, columnIndexes["acceptsMasterCard"]).setValue(data["acceptsMasterCard"]);
sheet.getRange(rowStart, columnIndexes["acceptsVisa"]).setValue(data["acceptsVisa"]);
sheet.getRange(rowStart, columnIndexes["acceptsFinancing"]).setValue(data["acceptsFinancing"]);
sheet.getRange(rowStart, columnIndexes["acceptsGoogleCheckout"]).setValue(data["acceptsGoogleCheckout"]);
sheet.getRange(rowStart, columnIndexes["acceptsPaypal"]).setValue(data["acceptsPaypal"]);
sheet.getRange(rowStart, columnIndexes["hasPrimaryServiceLocation"]).setValue(data["hasPrimaryServiceLocation"]);
sheet.getRange(rowStart, columnIndexes["primaryServiceLocationRadius"]).setValue(data["primaryServiceLocationRadius"]);
sheet.getRange(rowStart, columnIndexes["isListingClaimedByClient"]).setValue(data["isListingClaimedByClient"]);
sheet.getRange(rowStart, columnIndexes["isListingOptimized"]).setValue(data["isListingOptimized"]);
sheet.getRange(rowStart, columnIndexes["logoUrl"]).setValue(data["logoUrl"]);
sheet.getRange(rowStart, columnIndexes["imageUrls"]).setValue(data["imageUrls"]);
sheet.getRange(rowStart, columnIndexes["videoUrls"]).setValue(data["videoUrls"]);
sheet.getRange(rowStart, columnIndexes["isStreetAddressHidden"]).setValue(data["isStreetAddressHidden"]);
sheet.getRange(rowStart, columnIndexes["isListingVerified"]).setValue(data["isListingVerified"]);
sheet.getRange(rowStart, columnIndexes["isListingUnclaimed"]).setValue(data["isListingUnclaimed"]);
sheet.getRange(rowStart, columnIndexes["countryId"]).setValue(data["countryId"]);
sheet.getRange(rowStart, columnIndexes["isBingPinVerified"]).setValue(data["isBingPinVerified"]);
sheet.getRange(rowStart, columnIndexes["isGooglePinVerified"]).setValue(data["isGooglePinVerified"]);
sheet.getRange(rowStart, columnIndexes["isYahooPinVerified"]).setValue(data["isYahooPinVerified"]);
rowStart++;
});
}else{
Browser.msgBox("No result to display");
}
}else{
Browser.msgBox("Error: " + response.message);
}
}
/*********************************************************************************************************************************************
* Online Presence
**********************************************************************************************************************************************/
function loadOnlinePresence(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
var rowStart = 6;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
var columnIndexes = {
"monthToDate": 1,
"lifeToDate": 2
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["monthToDate"]).setValue('');
sheet.getRange(row, columnIndexes["lifeToDate"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/onlinepresence';
var response = getData(url);
if(response.status=='success'){
var data = response.result;
sheet.getRange(rowStart, columnIndexes["monthToDate"]).setValue(data["monthToDate"]);
sheet.getRange(rowStart, columnIndexes["lifeToDate"]).setValue(data["lifeToDate"]);
}else{
Browser.msgBox("Error: " + response.message);
}
}
/*********************************************************************************************************************************************
* Required Actions
**********************************************************************************************************************************************/
function loadRequiredActions(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
var startdate = sheet.getRange(2, 2).getDisplayValue();
var enddate = sheet.getRange(3, 2).getDisplayValue();
var rowStart = 6;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
//Logger.log(rowStart + ' - ' + rowEnd);
var columnIndexes = {
"taskId": 1,
"categoryName": 2,
"taskName": 3,
"completedDate": 4,
"minutesSpent": 5,
"actionUrl": 6
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["taskId"]).setValue('');
sheet.getRange(row, columnIndexes["categoryName"]).setValue('');
sheet.getRange(row, columnIndexes["taskName"]).setValue('');
sheet.getRange(row, columnIndexes["completedDate"]).setValue('');
sheet.getRange(row, columnIndexes["minutesSpent"]).setValue('');
sheet.getRange(row, columnIndexes["actionUrl"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/tasks/completed?startdate=' + startdate + '&enddate=' + enddate;
Logger.log(url);
var response = getData(url);
if(response.status=='success'){
var total = response.result.length;
if(total > 0){
response.result.forEach(function(data) {
sheet.getRange(rowStart, columnIndexes["taskId"]).setValue(data["taskId"]);
sheet.getRange(rowStart, columnIndexes["categoryName"]).setValue(data["categoryName"]);
sheet.getRange(rowStart, columnIndexes["taskName"]).setValue(data["taskName"]);
sheet.getRange(rowStart, columnIndexes["completedDate"]).setValue(data["completedDate"]);
sheet.getRange(rowStart, columnIndexes["minutesSpent"]).setValue(data["minutesSpent"]);
sheet.getRange(rowStart, columnIndexes["actionUrl"]).setValue(data["actionUrl"]);
rowStart++;
});
}else{
Browser.msgBox("No result to display");
}
}else{
Browser.msgBox("Error: " + response.message);
}
}
/*********************************************************************************************************************************************
* Total Work
**********************************************************************************************************************************************/
function loadTotalWork(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
var rowStart = 6;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
var columnIndexes = {
"totalWork": 1,
"currentMonth": 2
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["totalWork"]).setValue('');
sheet.getRange(row, columnIndexes["currentMonth"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/work/total';
var response = getData(url);
if(response.status=='success'){
var totalwork = response.result;
sheet.getRange(rowStart, columnIndexes["totalWork"]).setValue(totalwork);
}else{
Browser.msgBox("Error: " + response.message);
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/work/total/currentmonth';
var response = getData(url);
if(response.status=='success'){
var currentmonth = response.result;
sheet.getRange(rowStart, columnIndexes["currentMonth"]).setValue(currentmonth);
}else{
Browser.msgBox("Error: " + response.message);
}
}
/*********************************************************************************************************************************************
* Total Work By Category
**********************************************************************************************************************************************/
function loadTotalWorkByCategory(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
var rowStart = 6;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
var columnIndexes = {
"categoryId": 1,
"name": 2,
"count": 3
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["categoryId"]).setValue('');
sheet.getRange(row, columnIndexes["name"]).setValue('');
sheet.getRange(row, columnIndexes["count"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/work/total/bycategory';
Logger.log(url);
var response = getData(url);
if(response.status=='success'){
var total = response.result.length;
if(total > 0){
response.result.forEach(function(data) {
sheet.getRange(rowStart, columnIndexes["categoryId"]).setValue(data["categoryId"]);
sheet.getRange(rowStart, columnIndexes["name"]).setValue(data["name"]);
sheet.getRange(rowStart, columnIndexes["count"]).setValue(data["count"]);
rowStart++;
});
}else{
Browser.msgBox("No result to display");
}
}else{
Browser.msgBox("Error: " + response.message);
}
}
/*********************************************************************************************************************************************
* Total Work Hours By Month
**********************************************************************************************************************************************/
function loadTotalWorkHoursByMonth(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
var numberofmonths = sheet.getRange(2, 2).getValue();
var rowStart = 6;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
var columnIndexes = {
"month": 1,
"year": 2,
"hoursCompleted": 3,
"hoursPending": 4
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["month"]).setValue('');
sheet.getRange(row, columnIndexes["year"]).setValue('');
sheet.getRange(row, columnIndexes["hoursCompleted"]).setValue('');
sheet.getRange(row, columnIndexes["hoursPending"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/work/hours/bymonth?numberofmonths=' + numberofmonths;
Logger.log(url);
var response = getData(url);
if(response.status=='success'){
var total = response.result.length;
if(total > 0){
response.result.forEach(function(data) {
sheet.getRange(rowStart, columnIndexes["month"]).setValue(data["month"]);
sheet.getRange(rowStart, columnIndexes["year"]).setValue(data["year"]);
sheet.getRange(rowStart, columnIndexes["hoursCompleted"]).setValue(data["hoursCompleted"]);
sheet.getRange(rowStart, columnIndexes["hoursPending"]).setValue(data["hoursPending"]);
rowStart++;
});
}else{
Browser.msgBox("No result to display");
}
}else{
Browser.msgBox("Error: " + response.message);
}
}
/*********************************************************************************************************************************************
* Running Total Work By Category
**********************************************************************************************************************************************/
function loadRunningTotalWorkByCategory(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
var categoryid = sheet.getRange(2, 2).getValue();
var date = sheet.getRange(3, 2).getDisplayValue();
var rowStart = 6;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
var columnIndexes = {
"categoryId": 1,
"name": 2,
"count": 3
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["categoryId"]).setValue('');
sheet.getRange(row, columnIndexes["name"]).setValue('');
sheet.getRange(row, columnIndexes["count"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/work/categories/' + categoryid + '/total?date=' + date;
var response = getData(url);
if(response.status=='success'){
var total = response.result.length;
if(total > 0){
response.result.forEach(function(data) {
sheet.getRange(rowStart, columnIndexes["categoryId"]).setValue(data["categoryId"]);
sheet.getRange(rowStart, columnIndexes["name"]).setValue(data["name"]);
sheet.getRange(rowStart, columnIndexes["count"]).setValue(data["count"]);
rowStart++;
});
}else{
Browser.msgBox("No result to display");
}
}else{
Browser.msgBox("Error: " + response.message);
}
}
/*********************************************************************************************************************************************
* Completed Customer Work
**********************************************************************************************************************************************/
function loadCompletedCustomerWork(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var customerid = sheet.getRange(1, 2).getValue();
var categoryid = sheet.getRange(2, 2).getValue();
var startdate = sheet.getRange(3, 2).getDisplayValue();
var enddate = sheet.getRange(4, 2).getDisplayValue();
var rowStart = 7;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
var columnIndexes = {
"date": 1,
"name": 2,
"url": 3
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["date"]).setValue('');
sheet.getRange(row, columnIndexes["name"]).setValue('');
sheet.getRange(row, columnIndexes["url"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/work/categories/' + categoryid + '/completed?startdate=' + startdate + '&enddate=' + enddate;
var response = getData(url);
if(response.status=='success'){
var total = response.result.length;
if(total > 0){
response.result.forEach(function(data) {
sheet.getRange(rowStart, columnIndexes["date"]).setValue(data["date"]);
sheet.getRange(rowStart, columnIndexes["name"]).setValue(data["name"]);
sheet.getRange(rowStart, columnIndexes["url"]).setValue(data["url"]);
rowStart++;
});
}else{
Browser.msgBox("No result to display");
}
}else{
Browser.msgBox("Error: " + response.message);
}
}
/*********************************************************************************************************************************************
* Completed Customer Work
**********************************************************************************************************************************************/
function loadCompletedCustomerWork(){
var active = SpreadsheetApp.getActive();
var sheet = active.getActiveSheet();
var rowStart = 6;
var rowEnd = getFirstEmptyRow(sheet, rowStart) - 1;
var columnIndexes = {
"categoryId": 1,
"categoryName": 2
};
//clear result first
if(rowEnd >= rowStart){
for(var row=rowStart;row<=rowEnd;row++){
sheet.getRange(row, columnIndexes["categoryId"]).setValue('');
sheet.getRange(row, columnIndexes["categoryName"]).setValue('');
}
}
var url = 'https://service.boostability.com/api/partner/v3/work/categories';
var response = getData(url);
if(response.status=='success'){
var data = response.result;
for(var key in data){
sheet.getRange(rowStart, columnIndexes["categoryId"]).setValue(key);
sheet.getRange(rowStart, columnIndexes["categoryName"]).setValue(data[key]);
rowStart++;
}
}else{
Browser.msgBox("Error: " + response.message);
}
}
/*********************************************************************************************************************************************
* Custom Report
**********************************************************************************************************************************************/
function loadTopRank() {
var active = SpreadsheetApp.getActive();
var sheets = active.getActiveSheet();
var customerid = sheets.getRange(1, 2).getValue();
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/keywordrankings/analytics/week/current';
var options = {
'contentType' : 'application/json',
'headers' : {
'Authorization' : '<token>'
}
};
var response = UrlFetchApp.fetch(url, options);
var status = response.getResponseCode();
if(status==200){
var result = JSON.parse(response.getContentText());
sheets.getRange(6, 1).setValue(result.topRankingKeyword);
sheets.getRange(6, 4).setValue(result.currentRank);
sheets.getRange(6, 7).setValue(result.keywordsOnFirstPage);
sheets.getRange(12, 1).setValue(result.avgRank);
}else{
sheets.getRange(6, 1).setValue('Error');
sheets.getRange(6, 4).setValue('Error');
sheets.getRange(6, 7).setValue('Error');
sheets.getRange(12, 1).setValue('Error');
}
//total online presence
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/onlinepresence';
var response = UrlFetchApp.fetch(url, options);
var status = response.getResponseCode();
if(status==200){
var result = JSON.parse(response.getContentText());
sheets.getRange(12, 4).setValue(result.lifeToDate);
}else{
sheets.getRange(12, 4).setValue('Error');
}
//total completed items complete
var url = 'https://service.boostability.com/api/partner/v3/customers/' + customerid + '/work/total';
var response = UrlFetchApp.fetch(url, options);
var status = response.getResponseCode();
if(status==200){
var result = JSON.parse(response.getContentText());
sheets.getRange(18, 1).setValue(result);
}else{
sheets.getRange(18, 1).setValue('Error');
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment