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
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;
}) :
[];
}