Smartsheet is a powerful, spreadsheet-like tool. It helps to automate processes in your team. In my current company, we've been using it for years to support project management. Plans, issue logs, risk registers, project dashboards, task trackers - just to name a few examples of what we use Smartsheet for.

However, sometimes you need to have data from Smartsheet copied into Google Sheets. It might be because you already have some data maintained in Google Sheets and you would like to integrate data sets across both places. Or, you would like to leverage more sophisticated charts that are available in Sheets. Whatever the reason is, synchronization should be done automatically and regularly.

I've recently encountered exactly the case when I needed to be regularly copying data from Smartsheet to Google Sheets. Quick search on Github led me to this simple Google Appscript, which uses API to read the data from Smartsheet. The code is also listed below:

function run() {
    var smartSheetID = "ID_SMARTSHEET";
    var smartSheetToken = "TOKEN";
    var sheet = SpreadsheetApp.openById("SHEET_ID").getSheets()[0];
    var url = "https://api.smartsheet.com/2.0/sheets/" + smartSheetID;
    var response = UrlFetchApp.fetch(
        url, { headers: {Authorization: 'Bearer ' + smartSheetToken}}
    );
    var result = JSON.parse(response.getContentText());
    sheet.clear();
    for(var row in result.rows){
        var cells = result.rows[row]["cells"];
        var tab = [];
        for(var cell in cells){
            var value = cells[cell]["value"] == undefined ? "" : cells[cell]["value"];
            tab.push(value);
        }
        sheet.appendRow(tab);
    }
}

What you need to do next is open Script editor (Tools --> Script editor) on a Google sheet where you want to store the data in. Paste in the script code to the editor. And then, change the following variables to your unique values:

• ID_SMARTSHEET: ID of the smartsheet you want to synchronize with. You can get this ID from the smartsheet properties.
• TOKEN: A secret token that allows the script to access your Smartsheet account. To get the token, go to Smartsheet's Personal Settings --> API Access --> Generate new access token.
• SHEET_ID: ID of the Google sheet which you want to poplate with data from the smartsheet.

Now, you can run the script within the Script editor. For that, just press the "run" icon in the toolbar menu.

In a few seconds, depending how huge the original smartsheet is, the data will be copied over to Google Sheets. Notice that it's one way sync: Smartsheet to Sheets, not the other way around. If you make any changes in the Google sheet, they won't be synced back to the smartsheet.

If you want to run the script regularly, select in the Script editor: Edit --> Current project's triggers. That will lead you to another page, where you can create a new trigger and choose time-driven event source.

I've been testing this script for a few weeks now. It's been working like a charm. This integration has helped my organization to automate one of the critical processes. I hope it will help you too!