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

Bharathi2024

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.

Thanks

Leibel S

Bharathi2024

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

https://developers.smartsheet.com/blog/find-columns-quicker-with-a-column-map

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)
console.log(csvData)

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.