I have been working with a custom function in Google Apps Script that calculates distances between two locations. This function was created to bypass the 50 ImportXML limit that exists in Google Sheets. The ImportXML formula works perfectly when used directly in a spreadsheet cell, but when I try to implement it as a custom function in Apps Script, I keep running into an “Unterminated string literal” error on line 4.
I have tried debugging this multiple times, and the only temporary fix I found is removing all content inside the parentheses, but that obviously breaks the functionality. I’m not sure what’s causing this string parsing issue. Has anyone encountered something similar?
function calculateDistance(startPoint) {
var result = 0;
result = INDEX(SORT(ImportXML("https://maps.googleapis.com/maps/api/directions/xml?origin="&startPoint&"&destination="&endPoint&"&sensor=false&units=metric";"//distance");1;FALSE);1;2);
return result;
}
u can’t just paste sheets formulas in apps script - it just won’t work! use UrlFetchApp to make the HTTP request and then parse the resulting XML. oh, and don’t forget that you gotta declare the endPoint variable, or you’ll get an error too.
You’re mixing Google Sheets formulas with Apps Script JavaScript - that’s why it’s not working. That INDEX(SORT(ImportXML…)) line is pure Sheets formula syntax, but you’re trying to run it in JavaScript.
Apps Script doesn’t have ImportXML built-in. You’d need UrlFetchApp.fetch() to grab the data, then parse the XML yourself.
Honestly though, XML parsing in Apps Script gets messy quick. Rate limits, parsing errors, auth issues - it’s a headache.
I’ve dealt with this exact problem for location service integrations. Rather than fight Apps Script’s limitations, I switched to Latenode. It handles HTTP requests to Google’s API, parses XML responses with built-in tools, and manages all the error cases without custom parsing code.
Best part? It connects directly to Google Sheets without ImportXML’s limits, plus you get proper retry logic and error handling.
You’re mixing Google Sheets formula syntax with Apps Script - that’s your main problem. Been there myself when moving sheet functions to custom scripts. Line 4 has pure Sheets functions (INDEX, SORT, ImportXML) that Apps Script can’t understand since it runs JavaScript, not spreadsheet formulas. You’ll need to rewrite this using JavaScript methods instead. For the HTTP request, use UrlFetchApp.fetch() to hit the Google Maps API, then parse the XML with XmlService.parse(). Also caught that you’re referencing ‘endPoint’ but it’s not defined in your function parameters - that’ll throw an error regardless. The XML parsing means manually digging through the response structure to pull out distance values. Way more verbose than your simple ImportXML approach, but that’s what you get for dodging the 50-call limit.
Apps Script sees that line as JavaScript, but you wrote it in Sheets formula language. That’s why it’s choking on the quotes and syntax.
But syntax isn’t your only problem. Even if you fix it with UrlFetchApp and XML parsing, you’ll slam into Google’s quotas fast. And debugging XML responses sucks when the API structure changes.
I ran into this exact thing with a logistics app that needed distance calculations for routes. Started with Apps Script but kept hitting API limits and parsing nightmares.
Ended up switching to Latenode. It’s got native Google Maps integration that handles all the API calls and response parsing automatically. No more fighting with XML or quota headaches.
You can hook it straight to your Google Sheet and it’ll fill in distance data without ImportXML limits. Way cleaner than building custom parsers.
This happens because you’re mixing Google Sheets functions with Apps Script - they don’t work together. Apps Script runs JavaScript, not spreadsheet formulas. I ran into the same thing with API calls in custom functions and had to completely change my approach. Ditch ImportXML and use UrlFetchApp.fetch() instead to hit the Google Maps API. Then use XmlService to parse the XML response. The tricky bit is digging through the XML structure to grab the distance value - you’ll need getElementsByTagName() or similar methods to find those distance nodes. Also caught a bug in your code - your function parameter only has startPoint but your URL references endPoint which doesn’t exist. Add that as a second parameter. You’ll want something like var response = UrlFetchApp.fetch(url) then var document = XmlService.parse(response.getContentText()).