Smartsheet export rows to another Smartsheet

export.py

import smartsheet
import os
import logging
import sys
import datetime

# Smartsheet API 2.0 reference (Python)
# https://smartsheet-platform.github.io/api-docs/index.html?python#api-reference

def get_cell_by_column_name(row, column_ame):
    column_id = column_map1[column_ame]
    return row.get_column(column_id)

def appender_of_row(i):
    row_a = ss.models.Row()
    row_a.to_bottom = True
    print(i[0])
    row_a.cells.append({
        'column_id': column_map2["ID"],
        'value': i[0]
    })
    print(i[1])
    row_a.cells.append({
        'column_id': column_map2["Task Name"],
        'value': i[1]
    })
    print(i[2])
    row_a.cells.append({
        'column_id': column_map2["Due Date"],
        'value': i[2],
    })
    print(i[3])
    row_a.cells.append({
        'column_id': column_map2["Assigned To"],
        'value': i[3],
        'strict': False     #observed that this is needed for columns with contacts
    })
    # put additional column value
    now = datetime.datetime.now()
    print(now.strftime('%Y-%m-%d'))
    row_a.cells.append({
        'column_id': column_map2["Archived"],
        'value': now.strftime('%Y-%m-%d')
    })
    return row_a

# start of main app
# retrieve the API access token stored in an environment variable
access_token = os.environ['SMARTSHEET_TOKEN']
ss = smartsheet.Smartsheet(access_token)
ss.errors_as_exceptions(True)
logging.basicConfig(filename='rwsheet.log', level=logging.INFO)

#sheets' ID properties
sheet1_id = 306355723626372
sheet2_id = 2170568561715076

# Load entire sheet  -- new error-handling 
try:
    sheet1_obj = ss.Sheets.get_sheet(sheet1_id)
    sheet2_obj = ss.Sheets.get_sheet(sheet2_id)
except Exception as e:
    print(e)
    sys.exit(1)

# The API identifies columns by Id, but it's more convenient to refer to column names. Store a map here
column_map1 = {}

# Build column map for later reference - translates column names to column id
for column in sheet1_obj.columns:
    column_map1[column.title] = column.id

column_map2 = {}

# Build column map for later reference - translates column names to column id
for column in sheet2_obj.columns:
    column_map2[column.title] = column.id


# iterate rows on sheet1 from first to last row and get qualified rows to put to sheet2
sheet1_rows = []
for i in range(sheet1_obj.total_row_count):
    row_id = sheet1_obj.rows[i].id
    source_row = ss.Sheets.get_row(sheet1_id, row_id )    
    source_col = get_cell_by_column_name(source_row, "Done")

    #the condition: export to sheet2 only those rows ticked as Done
    #picking between the attributes .value and .display_value is tricky
    if source_col.value:  
        source_col = get_cell_by_column_name(source_row, "ID")
        c_id = source_col.value
        
        source_col = get_cell_by_column_name(source_row, "Task Name")
        c_task = source_col.value

        source_col = get_cell_by_column_name(source_row, "Due Date")
        c_ddate = source_col.value

        source_col = get_cell_by_column_name(source_row, "Assigned To")
        c_person = source_col.value

        sheet1_rows.append([c_id, c_task, c_ddate, c_person])
        print("Candidate rows:")
        for j in [c_id, c_task, c_ddate, c_person]:
            print(j)

# export qualified sheet1 rows to sheet2
if sheet1_rows:
    rowsToAdd = []
    print("Checking rows to put in sheet2")
    for row in sheet1_rows:
        response = ss.Search.search_sheet(sheet2_id, row[0]) #search for the unique autonumber ID
        if response.total_count == 0:
            # not found, ok to add
            rowToAdd = appender_of_row(row)
            rowsToAdd.append(rowToAdd)
    
    # push all rows to sheet2, as a bulk operation (mentioned in the API)
    if rowsToAdd:
        print("Exporting {} rows to sheet2".format(len(rowsToAdd)))
        ss.Sheets.add_rows(sheet2_id, rowsToAdd)
    else:
        print("No rows to export")