Trouble with XML and SOAP authentication in Google Sheets script editor

Other Questions Google docs
1

I’m having issues with a Google Sheets script that’s supposed to connect to an API using SOAP and WSDL. The main problem is creating the header XML object. It should have two parts: UserAuthentication and getQuota.

When I only include UserAuthentication, I get a server error. But when I try to add both parts, I get an authentication error. I’m not sure what I’m doing wrong.

I’ve made some progress though. The authentication header seems okay now, but I’m getting a new error: “Procedure ‘getTransactionList’ not present”. This is weird because it should be there.

Here’s a simplified version of my code:

function testAPIConnection() {
  var apiEndpoint = SoapService.wsdl("https://example-api.com/v1/Service?wsdl");
  var apiClient = apiEndpoint.getService(apiEndpoint.getServiceNames());

  var authHeader = [
    "auth:UserCredentials",
    { "SOAP-ENV:mustUnderstand": "true" },
    { "SOAP-ENV:actor": "https://example-api.com" },
    [ "auth:userId", "12345" ],
    [ "auth:userPassword", "secretpassword" ],
    [ "auth:userType", "customer" ]
  ];

  var quotaHeader = [
    "api:fetchQuota", "true"
  ];

  var params = [
    "api:fetchData",
    [ "api:startTime", "2023-01-01T00:00:00" ],
    [ "api:endTime", "2023-01-31T23:59:59" ],
    [ "api:dataType", "sales" ]
  ];

  var result = apiClient.getSoapEnvelope("fetchData", params, [authHeader, quotaHeader]);
  Logger.log(result);
}

Can anyone spot what might be causing these issues? Any help would be great!

2

I’ve encountered similar issues when working with SOAP APIs in Google Sheets. One thing that stands out is the structure of your headers. Instead of separate authHeader and quotaHeader, try combining them into a single header object. This often resolves authentication errors.

For the ‘Procedure not present’ error, double-check the API documentation. Sometimes the method names in the WSDL don’t match exactly with what’s expected. Try using ‘getTransactionList’ instead of ‘fetchData’ in your getSoapEnvelope call.

Also, ensure your API endpoint URL is correct and that you have the necessary permissions. Sometimes, API providers have different endpoints for testing and production, which can cause confusion.

Lastly, consider using the UrlFetchApp.fetch() method directly with a constructed SOAP envelope. This gives you more control over the request and can help diagnose issues more easily.

3

I’ve dealt with similar SOAP authentication headaches in Google Sheets. One thing that helped me was to double-check the order of elements in the XML. Some APIs are really picky about this.

Have you tried using a tool like SoapUI to test your requests outside of Google Sheets? It’s a lifesaver for debugging these kinds of issues. You can see exactly what’s being sent and received.

For the ‘Procedure not present’ error, I’d suggest looking at the WSDL file directly. Sometimes the method names aren’t what you’d expect. I once spent hours troubleshooting only to find out the method name was case-sensitive!

Also, don’t forget to check your API credentials. I’ve embarrassingly spent way too much time debugging only to realize my API key had expired. It’s always the simple things that get you!

Lastly, if all else fails, reach out to the API provider’s support. They might have insights into common pitfalls or recent changes that could be affecting your integration.

4

hey pete, I’ve run into similar probs. have u tried logging the entire SOAP request before sending? sometimes seeing the full XML helps spot issues. also, check if the API needs any special encoding for the auth credentials. oh, and make sure ur using the latest version of the API - they might’ve changed smthing recently. good luck!