Hi @ak12
So, to connect to the smartsheet API you need first:
- An access token to the API (documentation here)
- A python environment with the smartsheet SDK installed (smartsheet-python-sdk)
To connect to the smartsheet API, from a python notebook or recipe:
import smartsheet
# You need a Token to authenticate with smartsheet
TOKEN = dataiku.get_custom_variables()['SMARTSHEET_TOKEN'] # we store the token in a custom variable in our DSS instance, but you should change this by the string token
smartsheet_client = smartsheet.Smartsheet(TOKEN)
smartsheet_client.errors_as_exceptions(True)
This creates a client that we can use to interact with the sheets, and depending in your token permissions, you can add, update or delete sheets using code. You should start with the tutorial to understand the logic of the api.
But let's say you want to get a sheet as a pandas dataframe, first you need the sheet id, that you can get online from the smartsheet webapp, or by listing all the sheets and their ids:
sheets = smartsheet_client.Sheets.list_sheets(include_all=True)
for s in sheets.data:
print(f"The sheet \"{s.name}\" has id {s.id}")
Once you have find the id of sheet you need, you should create a python function to automate the parsing into a dataframe:
def simple_sheet_to_dataframe(sheet):
col_names = [col.title for col in sheet.columns]
rows = []
for row in sheet.rows:
cells = []
for cell in row.cells:
cells.append(cell.value)
rows.append(cells)
data_frame = pd.DataFrame(rows, columns=col_names)
return data_frame
Now we open the connection to the sheet on interest and parse the data into a dataframe:
sheet = smartsheet_client.Sheets.get_sheet(id) # id can be a string or an int
df = simple_sheet_to_dataframe(sheet)
df.head() # to inspect the header
In the previously linked tutorial you should find examples on how to read excel files and then insert them into smartsheet.
I hope this helps to start!
I.
