How to filter data from Smartsheet using a list from another sheet

Question

I am an amateur, self-taught programmer. My primary language is Python. Recently, I have started learning about the Smartsheet API and I love the potential it has. In my organization, we use Smartsheet for one of our processes.

Now, I will explain what my inputs are, and what I would like to achieve. We have a production schedule sheet with all of our raw data. That is the primary data source. Additionally, I have generated a list of SKUs for products that we produce, along with testing information. One of the columns is 'Micro Testing' and the options are 'Yes' or 'No'.

What I want to do, is filter the primary data list for any SKU which is on my item directory and has 'Micro Testing' = 'Yes' and then pull any row from the primary data sheet with a SKU that matches the item directory on the separate sheet, and has the column set to 'Yes' for micro testing.

The end result would be a sheet that pulls down all items from the primary data source which have a SKU that matches my item directory list and it does so by checking to see if the SKU has micro testing or not.

This is the current script that I have using the python-read-write-sheet.py from GitHub for Python. Please note, I have this currently set up to check a column on the primary data source called 'Micro Testing', but I can't change the primary data source at this time, so I am trying to find a work around to make it so I can just use my list of SKUs with micro testing to check against with an index match/collect call or something along those lines:

import smartsheet
import os
import logging

_dir = os.path.dirname(os.path.abspath(__file__))


column_map = {}


def get_cell_by_column_name(row, column_name):
    column_id = column_map[column_name]
    return row.get_column(column_id)

def search_rows_and_build_spreadsheet(source_row):
    
    # Find the cell and value we want to evaulate
    micro_testing = get_cell_by_column_name(source_row, "Micro Testing")
    micro_value = micro_testing.display_value
    
    if micro_value == 'Yes':
        
        # How can I set this up to generate a list of all products coming from production, which have micro testing as per my separate item directory?
        
        pass

print("Starting...")

# Initialize client using SMARTSHEET_ACCESS_TOKEN variable

smart = smartsheet.Smartsheet()
smart.errors_as_exceptions(True)

# Log all of the API calls to play it safe!

logging.basicConfig(filename='rwsheet.log', level=logging.INFO)

print("Logging in-process")

# IMPORT SHEET
production = smart.Sheets.import_xlsx_sheet(_dir + '/Production Schedule.xlsx', header_row_index=0)
sheet = smart.Sheets.get_sheet(production.data.id)

print("Loaded " + str(len(sheet.rows)) + " rows from sheet: " + sheet.name)

# BUILD COLUMN MAP

for column in sheet.columns:
    column_map[column.title] = column.id

# ACCUMULATE ROWS NEEDING UPDATE TO A LIST

rowsToUpdate = []

for row in sheet.rows:
    rowsToUpdate = search_rows_and_build_spreadsheet(row)
    if rowsToUpdate is not None:
        rowsToUpdate.append(rowsToUpdate)

# WRITE UPDATES BACK TO SMARTSHEET SHEET

if rowsToUpdate:
    print("Writing " + str(len(rowsToUpdate)) + " rows back to sheet id " + str(sheet.id))
    result = smart.Sheets.update_rows(results.data.id, rowsToUpdate)
else:
    print("No updates required")

print("Done")

Answer 1

You don't need code to do this. Make a report in Smartsheet that has a filter on the Micro Testing column. The report will only have the records and columns you want. People can make changes in the report just like they can in the original sheet but they only see the records that match the filter.