How to map columns and rows of a json file to get the values from a smartsheet API request

Hi Everyone,

I'm new to work on the Smartsheet integration using API.

I have a request to load the data from the Smartsheet to a database table using Azure Data Factory. Through Smartsheet API connection I could able to read the data in a json format. To load it a database table I have to map the columns and rows so that I can able to get it in a table format. When I went through the json file, columns are maintained in a separate array and rows are maintained in a separate array. Can I know how to map columns and rows of a json file to get the values? An example to map the columns and rows would be better to understand.



  • Leibel S
    Leibel S ✭✭✭✭✭✭


    You basically need to use the column array to get the index, and use that index to get a specific value from the cell array within each row.

    You can do this programmatically/Dynamically.

    See below link for a similar example of this (but not for the index).

  • Lee Joramo
    Lee Joramo ✭✭✭✭✭✭

    I use the mapping methods that @Bharathi2024 linked to all of the time. One of the standard functions I have written for loading sheets creates mapping both from `ColumnID -> Name` and `Name -> ColumnID`

    However for your use case, it maybe easier to request the sheet as CSV, which will include the column names as the first row. Here is an example of javascript code, but I think all of the Smartsheet SDK's provide similar functions:

    const smartSDK = require('smartsheet')
    const smartClient = smartSDK.createClient(
        { "accessToken": "YOUR_TOKEN"}
    let options = {
        id: YOUR_SHEET_ID
    let csvData = await smartClient.sheets.getSheetAsCSV(options)

    I don't think the current version of the API documentation mentions the getSheetAsCSV function. But if you look through the SDK code for your language of choice you should find it.