Formula on Smartsheets API or google apps script that links smartsheet data to google sheets?

Abbie G

I would like to link the smartsheet data to google sheets so that it is updated at real-time. I cannot download apps and I need to figure out a script to link them together. Your help would be much appreciated!

Genevieve P.

Hi @Abbie G

Have you looked into Data Shuttle as an add-on to your Smartsheet plan? With Data Shuttle you can create an offload workflow which will update your Google Sheet with your Smartsheet data, see this Help Article: Data Shuttle by Smartsheet and more information here.

Cheers,

Genevieve

Allister

Hey Abbie,

This is only for pulling smartsheet data to a google sheet, not sure if you were looking for a back and forth connection, but this works.

You'll also need to set up Triggers in your apps script (I prefer time driven) to keep the connection live. Also, make sure you save the smartsheet after you update otherwise the script only pulls based on the last save.

function requestSmartsheet() {

    var smartSheetID = "[Enter SmartSheet ID Here]";

    var smartSheetToken = "[Enter Smartsheet Token Here]";

    var sheetName = "[Enter Googlesheet Tab Name Here]";

    var sheet = SpreadsheetApp.openById("[Enter Googlesheet ID Here]").getSheetByName(sheetName);

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

    var tabResult = [];

    var colResult = [];

  sheet.clear();

    for(var col in result.columns){

        colResult.push(result.columns[col]["title"]);

    }

    tabResult.push(colResult);

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

        }

        tabResult.push(tab);

    }

    sheet.getRange(1, 1, tabResult.length, tabResult[0].length).setValues(tabResult);

}

Bradley A

Hey @Allister I tried to use this script but it returns the error: Exception: Unexpected error while getting the method or property openById on object SpreadsheetApp. when it gets to line 9, any workarounds?