This is a follow-up on this post. Since the time I published it, I've made a couple of improvements that I wanted to share here.

First, I've started using Smartsheet reports as the data source instead of sheets. It's because there might be occasional changes to the underlying sheet (like moving columns or adding new columns) that could disrupt the order of data. To avoid this undesired effect, I create a new report in Smartsheet which is used solely for synchronization. This warrants a more robust and stable solution except when a column is renamed in the underlying sheet. Due to a known bug or feature, a Smartsheet report doesn't pick up new column names. However, as renaming doesn't happen too often, we can live with that.

Second, I've added a custom menu in the Google sheet. This is helpful for those who want to pull data from the Smartsheet report on demand. Whenever they need to refresh the data, they just go into the Google sheet and select the "Smartsheet" -> "Sync up" menu option. This gets the sync-up process started.

Below is the updated code you need to copy into the script editor. Alternatively, you can make a copy of this google spreadsheet which contains the code inside (open "Tools" → "Script editor").

var smartSheetToken = "TOKEN"; //Fill in your actual Smartsheet token
var smartReportID = "Report ID"; //Fill in your actual Smartsheet report from which you want to pull data

function onOpen() {

  var ui = SpreadsheetApp.getUi();  
  ui.createMenu('Smartsheet')
      .addItem('Sync Up', 'sync')      
      .addToUi();
}

function sync() {

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    var url = "https://api.smartsheet.com/2.0/reports/" + smartReportID;
    var response = UrlFetchApp.fetch(
        url, { headers: {Authorization: 'Bearer ' + smartSheetToken}}
    );
    var result = JSON.parse(response.getContentText());

    sheet.clear();

    var tab = [];

    for (var column in result.columns){ 
      var value = result.columns[column]["title"];
      tab.push(value);
    }

    sheet.appendRow(tab);

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