Loop through sheets and append columns

Question

I have over 100 sheets in a smartsheet workspace. I'd like to use the python api to loop through each sheet and append the row id, sheet id, and the primary column into an array or pandas data frame.

import requests
import pandas as pd
import io
import warnings
warnings.filterwarnings(action='once')
import smartsheet
import os.path
from time import gmtime, strftime
import pandas as pd
import numpy as np
import json
import requests
import hashlib

fullList = pd.DataFrame()

for sheet in sheetArray:
    r = requests.get(baseURL + "/" + str(sheet), headers=headers)
    rows = json.loads(r.text)
    rows = rows['rows']
    rowsDF = pd.DataFrame.from_dict(rows)
    dropCols = ["cells","createdAt", "expanded", "modifiedAt","siblingId"]
    rowsDF = rowsDF.drop(dropCols, axis=1)
    fullList.append(rowsDF)

Answer 1

I'm not sure about pandas, but I can help you get the information into a python array.

Using the Smartsheet Python SDK you'll want to first install the SDK, then import smartsheet.

Next, initialize a Smartsheet object with your access token like so

ss_client = smartsheet.Smartsheet(SMARTSHEET_ACCESS_TOKEN)

Grab your Workspace

workplace = ss_client.Workspaces.get_workspace(workplace_id)

Grab the sheets from the Workspace

wp_sheets = workplace.sheets

Initialize the array you're creating

info_array = []

Loop over the sheets from the Workspace object. These sheet objects only have a few fields to identify the sheet, so you'll need to use the sheet.id to get the full sheet from the Smartsheet API.

# loop through sheets 
for sheet in wp_sheets:
    # get sheet
    full_sheet = ss_client.Sheets.get_sheet(sheet.id)

Grab the primary column for the sheet

# get the primary column
primary_column_id = get_primary_column_id(full_sheet.columns)

The get_primary_column_id() function would look like this. The column objects have a boolean field for primary. Find the column with primary set to true.

def get_primary_column_id(columns):
    for column in columns:
        if (column.primary):
            return column.id

Grab the row Ids and append all the info to the info_array.

# get row ids
for row in full_sheet.rows:
    info_array.append({'sheet_id': sheet.id, 
    'row_id': row.id, 
    'primary_column_id': primary_column_id})  

Here's the Gist.