How can I call to Smartsheet API using Google Apps script?

Question

I have used Postman and Charles to see if my Smartsheet GET function works, and all is well, I get the data json string back.

I have tried running the call from local code and from a Google app script html page.

But I get this error from the Google app script page:

"XMLHttpRequest cannot load https://api.smartsheet.com/2.0/sheets/ MY SMART SHEET ID. Response to preflight request doesn't pass access control check: No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'https://n-n662xy6uqbadudjpoghatx4igmurid667k365ni-script.googleusercontent.com' is therefore not allowed access."

It is my aim to update a Google sheet automatically from a Smartsheet sheet.

My Ajax request looks like this:

var settings = {
  "async": true,
  "crossDomain": true,
  "url": "https://api.smartsheet.com/2.0/sheets/SHEET_ID",
  "method": "GET",
  "headers": {
    "authorization": "Bearer MY_SECRET_ACCESS_TOKEN",
    "cache-control": "no-cache",
    "postman-token": "SOME_LONG_TOKEN"
  }
}

$.ajax(settings).done(function (response) {
  console.log(response);
});

Answer 1

You cannot call the Smartsheet API from client-side JavaScript due to the fact that the API doesn't support CORS at this time.

You can call the Smartsheet API directly from a Google Apps Script. In fact, we/Smartsheet publish two Google Add-ons that both use the Smartsheet API from scripts (1,2).

The Google apps-script-oauth2 project provides a complete example of using the Smartsheet API in their sample directory on GitHub. See samples/Smartsheet.gs.

With the OAuth token out of the way, you can make requests to the Smartsheet API like so:

var url = 'https://api.smartsheet.com/2.0/users/me';
var options = {
  'method': 'get'
  , 'headers': {"Authorization": "Bearer " + getSmartsheetService().getAccessToken()  }
};
var response = UrlFetchApp.fetch(url, options).getContentText();
Logger.log("email:" + JSON.parse(response).email);

Note that getSmartsheetService() in the above example is just like getDriveService() in Google's Readme except for Smartsheet. The full code is below:

function getSmartsheetService() {
    // Create a new service with the given name. The name will be used when
    // persisting the authorized token, so ensure it is unique within the
    // scope of the property store.
    return OAuth2.createService('scott_smartsheet')

        // Set the endpoint URLs, which are the same for all Google services.
        .setAuthorizationBaseUrl('https://app.smartsheet.com/b/authorize')
        .setTokenUrl('https://api.smartsheet.com/2.0/token')

        // Set the client ID and secret, from the Google Developers Console.
        .setClientId(SMARTSHEET_CLIENT_ID)
        .setClientSecret(SMARTSHEET_CLIENT_SECRET)

        // Set the name of the callback function in the script referenced
        // above that should be invoked to complete the OAuth flow.
        .setCallbackFunction('authCallback')

        // Set the property store where authorized tokens should be persisted.
        .setPropertyStore(PropertiesService.getUserProperties())

        // Set the scopes to request (space-separated for Google services).
        .setScope('READ_SHEETS')

        // Set the handler for adding Smartsheet's required SHA hash parameter to the payload:
        .setTokenPayloadHandler(smartsheetTokenHandler)
        ;
}