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)

Hi AME! What have you tried so far? Happy to help, but not sure where you're stuck. — stmcallister, Nov 15 '18 at 19:16

stmcallister · Accepted Answer · 2018-11-16T07:24:49.647

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.

Thanks! How can I get the primary column id from 100+ sheets? I believe the column Id varies from sheet to sheet. Can I look up column id by column name? — AME, Nov 16 '18 at 03:44
sorry about that. updated my answer with the code for get_primary_column_id() — stmcallister, Nov 16 '18 at 07:25
A strange thing happened when I modified this code to perform a specific action. My goal was to move all rows in sheets within the test workforce to a target sheet. I used this code to narrow down the list of rows that I needed to move. However, when I ran the code, code performed the desired action but in a different (production) workspace! — AME, Nov 20 '18 at 04:28

Loop through sheets and append columns

1 Answers1