Get Smartsheet Data as a Json -using Python

Let’s get the data from this smartsheet as Json via the Smartsheet API Integrations,

-First of all, you need to click on your smartsheet profile on top right corner, and choose Apps & Integrations

-A new window pops up! click API Access on the left and generate a new Token! you will only see that token only once after you generate it, so copy it and stash it somewhere secure!

-Go to smartsheet home, right clicking the sheet you need, and copy the sheet_id

-Now, that we have theToken and the sheet_id, let’s get to coding :)

1. Store both your token and sheet_id into your Environment Variables, your .bash_profile should look like, This is not a valid token or sheet_id :)

export BEARER = "Bearer HH8384H8sdkjil09"
export SHEET_ID = "3437432947347324"

And if you don’t know how to store credentials in environment variables, check out my other tutorial about storing python variables into .bash_profile file. https://medium.com/@somer_13379/how-to-use-python-environment-variables-and-keep-your-passwords-safe-f167b15712f7

2. Import all the required modules

import json
import csv
import requests
import os

3. Create a Function and start Accessing the token and the sheet_id stored in the environment variables

def getSmartsheet():
    
    Bearer = (os.environ.get('BEARER'))
    sheet_id = (os.environ.get('SHEET_ID'))

4. Create headers for the API and call the Bearer Variable,

headers = {
    'Authorization': Bearer,
    'Accept': 'text/csv',
}

4. Send the API Request + sheet_ID and the Headers which contain the Bearer token and the data we should expect, which in this case is csv.

response = requests.get('https://api.smartsheet.com/2.0/sheets/' + str(sheet_id), headers=headers)
    return response.text

csvData = getSmartsheet()

5. Create a new Data Set, and read the csv as a Python Dictionary

data = []
reader = csv.DictReader(csvData.split('\n'), delimiter=',')

6. Create a new function, and append the Dictionary data into the new data set

def processingJson():
     for rows in reader:
        dictData= rows
        data.append(dictData)

7. Finally Do the JSON dumps

        jsonData = json.dumps(data, indent=4)
        print(jsonData)

processingJson()

8. Complete Code!

import json
import csv
import requests
import os


def getSmartsheet():
    #Accessing the Credentials
    Bearer = (os.environ.get('BEARER'))
    sheet_id = (os.environ.get('SHEET_ID'))

    #Create Headers that contain Bearer Token
    headers = {
        'Authorization': Bearer,
        'Accept': 'text/csv',
    }

    # send the API request + token and Sheet_id
    response = requests.get('https://api.smartsheet.com/2.0/sheets/' + str(sheet_id), headers=headers)
    return response.text

csvData = getSmartsheet()


#Create a new data set
data = []
#Reader te CSV as a python Dcit
reader = csv.DictReader(csvData.split('\n'), delimiter=',')


def processingJson():
    #Append the Dict into the Data set
     for rows in reader:
        dictData= rows
        data.append(dictData)

        #json Dump
        jsonData = json.dumps(data, indent=4)
        print(jsonData)

processingJson()

9. Returned Json

[
    {
        "ID": "2278",
        "Year": "2019",
        "City": "Columbus",
        "Third year": "OH",
        "Country": "United States",
        "Phone Number": "56734211"
    }
]
[
    {
        "ID": "2278",
        "Year": "2019",
        "City": "Columbus",
        "Third year": "OH",
        "Country": "United States",
        "Phone Number": "56734211"
    },
    {
        "ID": "33",
        "Year": "2018",
        "City": "Minneapolis",
        "Third year": "MN",
        "Country": "United States",
        "Phone Number": "56473821"
    }
]
[
    {
        "ID": "2278",
        "Year": "2019",
        "City": "Columbus",
        "Third year": "OH",
        "Country": "United States",
        "Phone Number": "56734211"
    },
    {
        "ID": "33",
        "Year": "2018",
        "City": "Minneapolis",
        "Third year": "MN",
        "Country": "United States",
        "Phone Number": "56473821"
    },
    {
        "ID": "23",
        "Year": "2017",
        "City": "San Jose",
        "Third year": "CA",
        "Country": "United States",
        "Phone Number": "23465787"
    }
]
[
    {
        "ID": "2278",
        "Year": "2019",
        "City": "Columbus",
        "Third year": "OH",
        "Country": "United States",
        "Phone Number": "56734211"
    },
    {
        "ID": "33",
        "Year": "2018",
        "City": "Minneapolis",
        "Third year": "MN",
        "Country": "United States",
        "Phone Number": "56473821"
    },
    {
        "ID": "23",
        "Year": "2017",
        "City": "San Jose",
        "Third year": "CA",
        "Country": "United States",
        "Phone Number": "23465787"
    },
    {
        "ID": "45",
        "Year": "2016",
        "City": "Los Angles",
        "Third year": "CA",
        "Country": "United States",
        "Phone Number": "98674324"
    }
]
[
    {
        "ID": "2278",
        "Year": "2019",
        "City": "Columbus",
        "Third year": "OH",
        "Country": "United States",
        "Phone Number": "56734211"
    },
    {
        "ID": "33",
        "Year": "2018",
        "City": "Minneapolis",
        "Third year": "MN",
        "Country": "United States",
        "Phone Number": "56473821"
    },
    {
        "ID": "23",
        "Year": "2017",
        "City": "San Jose",
        "Third year": "CA",
        "Country": "United States",
        "Phone Number": "23465787"
    },
    {
        "ID": "45",
        "Year": "2016",
        "City": "Los Angles",
        "Third year": "CA",
        "Country": "United States",
        "Phone Number": "98674324"
    },
    {
        "ID": "26",
        "Year": "2015",
        "City": "New York",
        "Third year": "NY",
        "Country": "United States",
        "Phone Number": "46576879"
    }
]
[
    {
        "ID": "2278",
        "Year": "2019",
        "City": "Columbus",
        "Third year": "OH",
        "Country": "United States",
        "Phone Number": "56734211"
    },
    {
        "ID": "33",
        "Year": "2018",
        "City": "Minneapolis",
        "Third year": "MN",
        "Country": "United States",
        "Phone Number": "56473821"
    },
    {
        "ID": "23",
        "Year": "2017",
        "City": "San Jose",
        "Third year": "CA",
        "Country": "United States",
        "Phone Number": "23465787"
    },
    {
        "ID": "45",
        "Year": "2016",
        "City": "Los Angles",
        "Third year": "CA",
        "Country": "United States",
        "Phone Number": "98674324"
    },
    {
        "ID": "26",
        "Year": "2015",
        "City": "New York",
        "Third year": "NY",
        "Country": "United States",
        "Phone Number": "46576879"
    },
    {
        "ID": "27",
        "Year": "2014",
        "City": "Phoenix",
        "Third year": "AZ",
        "Country": "United States",
        "Phone Number": "29385764"
    }
]

Hope this helps someone :)