I am using Google Apps Script to access an API. This is the documentation for the API
This is the function I have written that the trigger calls
function pullTrigger(){
SpreadsheetApp.openByUrl(dataURL);
let token = getTokenFromAPI();
Logger.log("Starting");
SpreadsheetApp.flush();
Utilities.sleep(3000);
getShiftsBear(token);
SpreadsheetApp.flush();
Utilities.sleep(3000);
getMembersBear(token);
SpreadsheetApp.flush();
Utilities.sleep(3000);
getClientsBear(token);
SpreadsheetApp.flush();
Logger.log("Concluded");
}
When I run the function manually from inside Google Apps Script it behaves by returning 200 for the getTokenFromAPI and for each of the Shifts, Members and Clients functions. However when I use a trigger such as time or onFormSubmit/OnEdit the getToken returns 200 but everything else returns 401.
Why is it not working for a trigger? I have read about requiring asynchronous functions for APIs, could this be the issue?
The documentation also says you must register IP with the software developer but I have checked and they have said this has not been enabled.
Do I need to use Google Cloud to access external APIs with authentication?
Here is one of my functions just to give you an idea and check the auth.
function getShiftsBear(token){
Logger.log('Bearer Auth in header');
//var url3 = 'http://developers.entirerecruit.com/recruit-out/v1.0/GetShiftsByShiftDate?ShiftFromDate=2022-08-01&ShiftToDate=2022-08-07&Show_TimesheetVerified=false&%24count=true'; //all shifts
let url3 = urlBase;
let fromDate = getTodayDate();
let toDate = getTomorrowDate(); //need a way to get dates in
let para = 'GetShiftsByShiftDate?ShiftFromDate='+ fromDate +'&ShiftToDate=' + toDate + '&Show_TimesheetVerified=false&%24count=true' //combines date with URL - all shifts
url3 = url3 + para;
console.log("the url is - " + url3);
var auth = 'Bearer ' + token;
console.log(auth);
var response = UrlFetchApp.fetch(url3, {
method: 'GET',
headers: {
'Authorization': auth
},
muteHttpExceptions: true
});
Logger.log('Response Code: ' + response.getResponseCode());
if (responseCodeEscape(response.getResponseCode()) == 0){
Logger.log("fail " + response.getResponseCode());
return;
}
var content = response.getContentText();
//console.log(content);
var json = JSON.parse(content);
//console.log(json["value"]);
json = json["value"];
var keys = []
for(var key in json){
var arr = [key , json[key]["ShiftCtrlNumber"],json[key]["ServiceId"],json[key]["DeliveryId"],json[key]["ServiceName"],json[key]["DeliveryName"],json[key]["ShiftOrderedDate"],json[key]["ShiftDate"],json[key]["ShiftDay"],json[key]["ShiftType"],json[key]["Start"],json[key]["End"],json[key]["QualificationCode"],json[key]["ExpertiseCode"],json[key]["EmployeeId"],json[key]["OfficeName"],json[key]["PostCode"],json[key]["PriorityID"],json[key]["PriorityName"],json[key]["QualificationCode"],json[key]["State"],json[key]["StatusCode"],json[key]["StatusDescription"],json[key]["FirstName"],json[key]["LastName"],json[key]["IsBooked"],json[key]["IsVerified"],json[key]["Break"],json[key]["WkdHrs"],json[key]["StatusCode"],json[key]["StatusDescription"],json[key]["OfficeID"],json[key]["OfficeName"],json[key]["ProfessionalCode"],json[key]["ProfessionalName"],json[key]["AuthorizedPersonName"],json[key]["BookingRatio"],json[key]["OrderNo"],json[key]["BookedBy"],json[key]["LastUpdatedOn"]]
keys.push(arr);
}
var ss = SpreadsheetApp.openByUrl(dataURL).getSheetByName("shifts_dump");
var headers =ss.getRange('1:2').getValues();
ss.clear();
ss.getRange('1:2').setValues(headers);
console.log(keys.length);
if (keys.length > 0){
ss.getRange(2,2,keys.length,40).setValues(keys);
}
}