How to generate redirect URI for Smartsheet to pass data to Apps Script

Question

Screenshot of smartsheet and google setup screens

When attempting to get data out of Smartsheet, I'm encountering an error that says the redirect URI is missing or invalid when I follow the link that was logged by my apps script project.

I've generated a client ID and client secret on both google and smartsheet but I don't know what to do next.

Google Credentials: I'm not sure what to put in the redirect Url section or the authorized Javascript origins at the link below.

https://console.developers.google.com/apis/credentials/oauthclient/########################2d.apps.googleusercontent.com?project=project-id-##############

Smartsheet Credentials: I have activated my Smartsheet Developer profile and generated a client ID and client secret for my app that I've called 'Google Sheets'

Shown below is the code that I have right now which I found on gitHub.

var CLIENT_ID = '...'; // what do I put here?
var CLIENT_SECRET = '...';  // what do I put here?

/**
* Authorizes and makes a request to the Smartsheet API.
*/
function run()
{
    var service = getService();
    if (service.hasAccess())
    {
        var url = 'https://api.smartsheet.com/2.0/users/me';
        var response = UrlFetchApp.fetch(url,
        {
        headers:
        {
            Authorization: 'Bearer ' + service.getAccessToken()
        }
        });
        var result = JSON.parse(response.getContentText());
        Logger.log(JSON.stringify(result, null, 2));
    }
    else
    {
        var authorizationUrl = service.getAuthorizationUrl();
        Logger.log('Open the following URL and re-run the script: %s', authorizationUrl);
    }
}

/**
* Reset the authorization state, so that it can be re-tested.
*/
function reset()
{
    getService().reset();
}

/**
* Configures the service.
*/
function getService()
{
    return OAuth2.createService('Smartsheet')
    // Set the endpoint URLs.
    .setAuthorizationBaseUrl('https://app.smartsheet.com/b/authorize')
    .setTokenUrl('https://api.smartsheet.com/2.0/token')

    // Set the client ID and secret.
    .setClientId(CLIENT_ID)
    .setClientSecret(CLIENT_SECRET)
    // Set the name of the callback function that should be invoked to
    // complete the OAuth flow.
    .setCallbackFunction('authCallback')
    // Set the property store where authorized tokens should be persisted.
    .setPropertyStore(PropertiesService.getUserProperties())
    // Scopes to request
    .setScope('READ_SHEETS')
    // Set the handler for adding Smartsheet's required SHA hash parameter to
    // the payload:
    .setTokenPayloadHandler(smartsheetTokenHandler);
}

/**
* Handles the OAuth callback.
*/
function authCallback(request)
{
        var service = getService();
        var authorized = service.handleCallback(request);
    if (authorized)
    {
        return HtmlService.createHtmlOutput('Success!');
    }
    else
    {
        return HtmlService.createHtmlOutput('Denied.');
    }
}

/**
* Adds the Smartsheet API's required SHA256 hash parameter to the access token
* request payload.
*/
function smartsheetTokenHandler(payload)
{
    var codeOrRefreshToken = payload.code ? payload.code : payload.refresh_token;
    var input = CLIENT_SECRET + '|' + codeOrRefreshToken;
    var hash = Utilities.computeDigest(
        Utilities.DigestAlgorithm.SHA_256, input, Utilities.Charset.UTF_8);
    hash = hash.map(function(val)
    {
        // Google appears to treat these as signed bytes, but we need them
        // unsigned.
        if (val < 0)
        {
            val += 256;
        }
        var str = val.toString(16);
        // pad to two hex digits:
        if (str.length == 1)
        {
            str = '0' + str;
        }
        return str;
    });
        payload.hash = hash.join('');
        // The Smartsheet API doesn't need the client secret sent (secret is verified
        // by the hash).
    if (payload.client_secret)
    {
        delete payload.client_secret;
    }
    return payload;
}

/**
* Logs the redict URI to register.
*/
function logRedirectUri()
{
    Logger.log(OAuth2.getRedirectUri());
}



function dataHandler(thing)
{
    thing = getData2();
    var rowTemp = thing.split(','), i, j, chunk = 7, rows = [];
for (i=0,j=rowTemp.length; i<j; i+=chunk)
{
    for(var k = 0; k<2; k++)
    {
    rowTemp[k+2] = new Date(rowTemp[k+2])
    }
    rows.push(rowTemp.slice(i,i+chunk));
}
Logger.log(rows);
}


var CLIENT_ID = 'SmartSheet Client ID'; // I'm not sure if this is
// supposed to come from google
// or smartsheet
var CLIENT_SECRET = 'Smartsheet Client Secret'; // Same here

/**
* Authorizes and makes a request to the Smartsheet API.
*/
function run() {
var service = getService();
if (service.hasAccess()) {
    var url = 'https://api.smartsheet.com/2.0/users/me';
    var response = UrlFetchApp.fetch(url, {
    headers: {
        Authorization: 'Bearer ' + service.getAccessToken()
    }
    });
    var result = JSON.parse(response.getContentText());
    Logger.log(JSON.stringify(result, null, 2));
} else {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s',
            authorizationUrl);
}
}

/**
* Reset the authorization state, so that it can be re-tested.
*/
function reset() {
getService().reset();
}

/**
* Configures the service.
*/
function getService()
{
    return OAuth2.createService('Smartsheet')
    // Set the endpoint URLs.
    .setAuthorizationBaseUrl('https://app.smartsheet.com/b/authorize')
    .setTokenUrl('https://api.smartsheet.com/2.0/token')
    // Set the client ID and secret.
    .setClientId(CLIENT_ID)
    .setClientSecret(CLIENT_SECRET)
    // Set the name of the callback function that should be invoked to
    // complete the OAuth flow.
    .setCallbackFunction('authCallback')
    // Set the property store where authorized tokens should be persisted.
    .setPropertyStore(PropertiesService.getUserProperties())
    // Scopes to request
    .setScope('READ_SHEETS')
    // Set the handler for adding Smartsheet's required SHA hash parameter to
    // the payload:
    .setTokenPayloadHandler(smartsheetTokenHandler);
}

/**
* Handles the OAuth callback.
*/
function authCallback(request)
{
    var service = getService();
    var authorized = service.handleCallback(request);
    if (authorized)
    {
        return HtmlService.createHtmlOutput('Success!');
    }
    else
    {
        return HtmlService.createHtmlOutput('Denied.');
    }
}

/**
* Adds the Smartsheet API's required SHA256 hash parameter to the access token
* request payload.
*/
function smartsheetTokenHandler(payload)
{
    var codeOrRefreshToken = payload.code ? payload.code : payload.refresh_token;
    var input = CLIENT_SECRET + '|' + codeOrRefreshToken;
    var hash = Utilities.computeDigest(
        Utilities.DigestAlgorithm.SHA_256, input, Utilities.Charset.UTF_8);
    hash = hash.map(function(val)
    {
        // Google appears to treat these as signed bytes, but we need them
        // unsigned.
        if (val < 0)
        {
            val += 256;
        }
        var str = val.toString(16);
        // pad to two hex digits:
        if (str.length == 1)
        {
            str = '0' + str;
        }
        return str;
    });
    payload.hash = hash.join('');
    // The Smartsheet API doesn't need the client secret sent (secret is verified
    // by the hash).
    if (payload.client_secret)
    {
        delete payload.client_secret;
    }
    return payload;
}

/**
* Logs the redict URI to register.
*/
function logRedirectUri()
{
    Logger.log(OAuth2.getRedirectUri());
}

Answer 1

I don't know much about Apps Script or the library that you are using, but you need to find the actual callback URI used by Apps Script and register that as the App Redirect URL in Smartsheet. It looks like the callback should be in the form https://script.google.com/macros/d/{SCRIPT ID}/usercallback (at least according to the library docs). That should issue the redirect which will eventually call your library authCallback with the authorization code for the token.

Here's another useful document of the process (but uses Node). https://developers.smartsheet.com/blog/creating-a-smartsheet-o-auth-flow-in-node-js

Answer 2

This is a complicated process that I have documented here: https://smartsheet-platform.github.io/api-docs/#third-party-app-development

If you still have questions after looking at this documentation/tutorial section, please keep asking. I'm here to help.