Access Smartsheet by column Name instead of Column Id

Question

I am a newbie working with Smartsheet, I am trying to access the values of columns and store them in a list, and I am able to access the values by the index. In my use case, people can delete random columns from the smartsheet and that can cause my index numbering affected while reading the data. For Example - My smartsheet looks like below:

Vin | Owner | Use | Location

123 abc test CA

456 xyz prod TX

The code I used so far to access the data is:

import smartsheet
from simple_smartsheet import Smartsheet
from simple_smartsheet.models import Sheet, Column, Row, Cell, ColumnType
from pprint import pprint
import pandas as pd
import re
import sasl
import json
from fastparquet import write
# from influxdb import InfluxDBClient
import thrift_sasl
import prestodb
import s3fs
import boto3
from pyhive import hive

smartsheet = smartsheet.Smartsheet('adjgdjcdjchbdclkcn')


# Get current user
#user_profile = smartsheet.Users.get_current_user()

# Get all columns.
MySheet = smartsheet.Sheets.get_sheet(1234567891234567)

Vin = []
Owner = []
Use = []
Location = []

def Data():
 for RowIndex in range(0, len(MySheet.rows)):
           Vin.append(MySheet.rows[RowIndex].cells[2].display_value)
           Use.append(MySheet.rows[RowIndex].cells[3].display_value)
           Owner.append(MySheet.rows[RowIndex].cells[4].display_value)
           Location.append(MySheet.rows[RowIndex].cells[5].display_value)
print(Vin)
print(Use)
Print(Owner)
Print(Location)

Result I want is (Using the column name instead of Index):

Vin = [123, 456]

Owner = [abc, xyz]

Use = [test, prod]

Location = [CA, TX]

Now Instead of accessing the values by index, how can I append the values in a list using column name instead? Any help or lead is much appreciated. Thank You in advance!

Answer 1

It's not a good idea to reference (in code) columns by name. Just as you've pointed out that index is subject to change if users delete columns in the sheet, column names are subject to change if users rename columns in the sheet. The only sure way to identify a column is by its ID, as once a column is created its ID will never change -- regardless of whether its index, name, data type, or anything else about the column is changed, the column ID will always remain the same.

If this is a one-time scenario -- i.e., the sheet that you're processing already exists and will be the one and only sheet you need to process like this -- then I'd suggest you issue a List Columns request (using Postman or similar tool) to identify the IDs of the columns you're interested in processing, and then use those IDs in your code to reference the columns.

Alternatively -- if you need your solution to be able to dynamically lookup column IDs for new sheets over time (instead of you having to manually get the IDs), and you're reasonably certain that the column names you're interested in will exist in every new sheet you process over time (e.g., Vin, Owner, Use, Location), you could have your code issue the List Columns request, and process the response storing the IDs for the column names you're interested in and throwing an error if any of the column names you're interested in are not found (do not exist in the sheet).

Answer 2

from documentation: to call a column by name you can create a small function to read all the names and make a small matrix for you:

def get_new_cell_by_column_name(cells, column_name):
    column_id = s_column_map_new[column_name]
    return cells.get_column(column_id)

then you call the matrix when you need the ID:

new_cell_id = get_new_cell_by_column_name(row_object, "Name of the Column")

Or when you need the value you just add:

cell_value = new_cell_id.value

To avoid column name changes, use reports for the general user and only give editor access to the original sheet to users that need to add or edit information.