Getting error in Urlfetchapp request from API

49 Views Asked by At

I have created this code and it used to work, but it has stopped working.

I checked the header requests using Requestly and I am getting the correct JSON response in the browser.

I am not sure why it is working in this case. I am getting the error message:Unauthorized. Response Code 401.

Can someone please investigate and let me know what has changed?

Use this URL to investigate the API and response: https://widget.nhsd.healthdirect.org.au/v1/widget/search?widgetId=9b5494f2-b4e6-495b-8d9c-e813dcebb7ca&types=%5Bservices_types%5D%3Ageneral+practice+service

An example URL to try would be https://api.nhsd.healthdirect.org.au/v5/healthcareServices/_search?requestContext.serviceDeliveryMethod=PHYSICAL&filter.serviceType.codes=nhsd%3A%2Freference%2Ftaxonomies%2Fsnomed-servicetype%2F788007007&responseControl.offset=0&responseControl.limit=20&location.proximity.near_distance=1000000&location.proximity.near=-33.87341360149675%2C151.21006456818213

function getDataFromAPI() {
  try {
    // Get URLs from the sheet named "URLs"
    var spreadsheetId = "1rg91FoGbBwmsSu3ZfP82dSBpmqe1xY3WObg1rfyQVck";
    var urlsSheetName = "URLs";
    var dataSheetName = "Data";

    var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    var urlsSheet = spreadsheet.getSheetByName(urlsSheetName);
    var dataSheet = spreadsheet.getSheetByName(dataSheetName);

    if (!urlsSheet) {
      throw new Error("Sheet named 'URLs' not found in the specified spreadsheet.");
    }

    // Get URLs from column C starting from row 2
    var lastRow = urlsSheet.getLastRow();
    var urls = urlsSheet.getRange(2, 3, lastRow - 1, 1).getValues().flat();

    if (!urls || urls.length === 0) {
      throw new Error("No URLs found in the sheet 'URLs'.");
    }

    // Iterate through each URL
    urls.forEach(function(url) {
      var headers = {
        "Accept": "*/*",
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36",
        "Accept-Language": "en-US,en;q=0.9",
        "Accept-Encoding": "gzip,deflate,br",
        "X-Api-Key": "XyS1Zywa5e9PcekAmgaid8MVnHrHbmdh7Hkzcv9a",
        "Connection": "keep-alive",
        "Referer": "https://widget.nhsd.healthdirect.org.au/"
      };

      var options = {
        "method": "GET",
        "contentType": "application/json",
        "headers": headers,
        "muteHttpExceptions": true
      };
      console.log(url);
      var response = UrlFetchApp.fetch(url, options);
      console.log(response);
      console.log(response.getContentText());
      var data = JSON.parse(response.getContentText());


      if (!data || !data._embedded || !data._embedded.healthcareServices) {
        throw new Error("Invalid or missing data from the API for URL: " + url);
      }

      // Extract data
      var extractedData = data._embedded.healthcareServices.map(function(service) {
        // Extracting serviceType.label from the array
        var serviceTypeLabel = service.serviceType && service.serviceType[0] ? service.serviceType[0].label : '';

        // Extracting addressLine2 from physicalLocation
        var addressLine2 = service.location && service.location.physicalLocation ? service.location.physicalLocation.addressLine2 : '';

        // Extracting additional location details
        var addressLine3 = service.location && service.location.physicalLocation ? service.location.physicalLocation.addressLine3 : '';
        var suburbLabel = service.location && service.location.physicalLocation && service.location.physicalLocation.suburb ? service.location.physicalLocation.suburb.label : '';
        var stateLabel = service.location && service.location.physicalLocation && service.location.physicalLocation.state ? service.location.physicalLocation.state.label : '';
        var postcode = service.location && service.location.physicalLocation ? service.location.physicalLocation.postcode : '';

        // Extracting contacts.value based on different contacts.valueType
        var websiteContacts = extractContactsByType(service.contacts, "Website");
        var phoneContacts = extractContactsByType(service.contacts, "Phone");
        var faxContacts = extractContactsByType(service.contacts, "Fax");
        var emailContacts = extractContactsByType(service.contacts, "Email");

        // Log the JSON response for one row of data
        Logger.log("JSON Response for One Row (URL: " + url + "): " + JSON.stringify(service));

        return [
          service.organisation.name,
          service._links.self.href,
          serviceTypeLabel,
          addressLine2,
          addressLine3,
          suburbLabel,
          stateLabel,
          postcode,
          websiteContacts.join(", "),
          phoneContacts.join(", "),
          faxContacts.join(", "),
          emailContacts.join(", ")
        ];
      });
      dataSheet.getRange("A2:L").clearContent();
      // Find the last row in the sheet and append data below it
      var lastDataSheetRow = dataSheet.getLastRow();
      var dataRange = dataSheet.getRange(lastDataSheetRow + 1, 1, extractedData.length, extractedData[0].length);
      dataRange.setValues(extractedData);

      Logger.log("Data appended to the sheet named 'Data' for URL: " + url);
    });

    Logger.log("All data appended to the sheet named 'Data' for all URLs.");
  } catch (error) {
    Logger.log("Error: " + error.message);
  }
}

// Helper function to extract contacts.value based on contacts.valueType
function extractContactsByType(contacts, valueType) {
  return contacts ?
    contacts
    .filter(function(contact) {
      return (
        contact.valueType &&
        contact.valueType.label &&
        contact.valueType.label.toLowerCase() === valueType.toLowerCase()
      );
    })
    .map(function(contact) {
      return contact.value;
    }) :
    [];
}
0

There are 0 best solutions below