2

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);
  }

}
  • your code missing declaration of `getTokenFromAPI` – Kos Aug 28 '22 at 20:54
  • @Kos thanks for your comment. I have written the function – Simon Murray Aug 28 '22 at 21:14
  • May be related/Possible duplicate: https://stackoverflow.com/a/63024816/ – TheMaster Aug 28 '22 at 21:20
  • Can you clarify the code you are using? As an example, where does the `urlBase` come from? – Kessy Aug 29 '22 at 14:52
  • @Kessy urlBase is the address for the Get functions of the API. const urlBase = 'http://developers.entirerecruit.com/recruit-out/v1.0/'; – Simon Murray Sep 01 '22 at 03:08
  • 1
    @TheMaster I think that post is on the right track. I tried calling the token using the function in sheets. This worked but I still could not get the rest of the functions to work – Simon Murray Sep 01 '22 at 03:09
  • Can you share more information based on the @TheMaster comment and your findings on it? – Kessy Sep 05 '22 at 10:32
  • I'm having much the same problem, but using Google's own People API for interacting with Google Contacts. A query works fine when the function is called from the Apps Script editor, but not when called from a trigger. My question is posted here: https://stackoverflow.com/questions/76350342/google-contacts-people-api-query-not-working-in-script-called-from-trigger-scr – Robert M. May 28 '23 at 21:17

0 Answers0