Can Domo Export Data to Google Sheets?

We have processors in our office that need a Data Feed from Domo because they have to type in supporting notes based on the data and keep record on Google Sheets. We want it in a form of a feed because the data comes from out in-house API system. The API system is already bridging that information over to Domo, I would just need that feed in Domo coming from our API to go to Google Sheets. Is there an ability?

Comments

  • kshah008
    kshah008 Contributor

    Hi all,

     

    Can anybody help @Rsls101 with their question? 

  • Thank you!
  • kamuela
    kamuela Domo Employee

    We announced a new developers center at Domopalooza last week which includes the introduction of the Domo API.  This will allow you retrieve data from a dataset in Domo to use outside of Domo.  You would need some technical skills but should be able to retrieve the data from Domo and then use a Google Sheets API to update your spreadsheet.

     

    Thanks,

    Sam

    I am no longer with Domo. Please @mention @Millhouse for connector specific questions.
  • kshah008
    kshah008 Contributor

    @Rsls101, did kamuela's reply help you out? 

  • Are there demo account credentials for DOMO I could use to write a blog post on how to do this?  Let me know.  Thanks!  

  • Thank you for checking, this is a great feature. I am pretty familiar with the API system. If not, our company has our advanced engineering dept who can also look into this feature for us. Is there an instruction how to get started since it's a new feature?

  • I am interested in the API docs as well.

  • @Rsls101, did any of the above replies help you out?

  • Hi, 

     

    just wanted to share a simple working solution via Google Apps Script that can be adapted quickly. I hope it's a good starting point for anyone wishing to import one or multiple DataSets to a Google Sheet. 

     

    Basic working logic:

     

    1) Input your desired DataSet ID. Could be a single set or a list of multiple ids.

    2) Input your API token

    3) Name the sheet that shall contain your data

    4) Name the sheet where to log the last update timestamp

     

    Before you can run the script, you must have obviously created the according sheets in the spreadsheet.

    After that, you're done.

     

    The script will basically check whether the B1 field in the update log sheet is empty or the contained timestamp value is later then the last update timestamp retrieved via Domo's API. If one of the conditions is fulfilled, the DataSet will be fetched and posted to the data sheet, if not, nothing happens.

     

    You can set triggers for the script to run by clicking on the clock icon.

     

    You can alter the first cell the data is copied to by changing the range values in the sheet.getRange(1, 1, csvData.length, , csvData[0].le...); line of the getDataSet() function. 

     

    var id = "DataSet id"
    var token = "bearer YOUR_TOKEN"
    var dataSheetName = "YOUR NAME HERE"
    var updatelogSheetName = "Update Log" //OR YOUR NAME

    function myFunction() {
    getLastUpdate(id);
    }

    function getDataSet(id) {

    var options = {
    "method" : "GET",
    headers: {
    Accept: "text/csv",
    Authorization: token
    },
    };
    var csvContent = UrlFetchApp.fetch("https://api.domo.com/v1/datasets/" + id + "/data?includeHeader=true&fileName=dump.csv", options).getContentText();
    var csvData = Utilities.parseCsv(csvContent);

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheetName);
    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
    }

    function getLastUpdate(id) {

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(updatelogSheetName);
    var lastUpdatedSheet = sheet.getRange(1,2).getValue();

    var options = {
    "method" : "GET",
    headers: {
    Accept: "application/json",
    Authorization: token
    },
    };
    var response = UrlFetchApp.fetch("https://api.domo.com/v1/datasets/" + id, options).getContentText();
    var json = JSON.parse(response);
    var lastUpdatedApi = json.updatedAt;

    if (lastUpdatedSheet == null || lastUpdatedSheet < lastUpdatedApi) {
    getDataSet(id);
    sheet.getRange(1, 1).setValue("Last Updated:");
    sheet.getRange(1, 2).setValue(lastUpdatedApi);
    } else {
    Logger.log("No new data; Last updated at: " + lastUpdatedSheet + "; Last tried at: " + lastUpdatedApi);
    }

    }