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);
});

Scott Willeke · Answer 1 · 2016-04-18T06:58:03.533

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)
        ;
}

I can use the fetchURL method mentioned by Mr.Robot. But the response only contains a limited amount of data from the smartsheet that I am accessing, why is that? 'function callSmartSheet(){ var params = { "headers": { "authorization": "Bearer ", "cache-control": "no-cache", }, "method": "GET", } var response = UrlFetchApp.fetch("https://api.smartsheet.com/2.0/sheets//", params); var responseCode = response.getResponseCode(); if (responseCode === 200){ Logger.log(response); } else { Logger.log(responseCode); } }' — Jason Allshorn, Apr 17 '16 at 09:41
What data are you missing? See the include parameter of the API docs for getSheet at http://smartsheet-platform.github.io/api-docs/?shell#get-sheet for information on how to make sure the information you want is returned. — Scott Willeke, Apr 18 '16 at 06:32

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Under external APIs under Google Apps Script API,

Google Apps Script can interact with APIs from all over the web.

Connecting to public APIs

Dozens of Google APIs are available in Apps Script, either as built-in services or advanced services. If you want to use a Google (or non-Google) API that isn't available as an Apps Script service, you can connect to the API's public HTTP interface through the URL Fetch service.

The following example makes a request to the YouTube API and returns a feed of videos that match the query skateboarding dog.

var url = 'https://gdata.youtube.com/feeds/api/videos?'
+ 'q=skateboarding+dog'
+ '&start-index=21'
+ '&max-results=10'
+ '&v=2';
var response = UrlFetchApp.fetch(url);
Logger.log(response);

Here is a related SO ticket that connected his code in google apps script to smartsheet api.

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

2 Answers2