Import Indented Excel File to Smartsheet

import_indented_excel_to_ss.py

import smartsheet

access_token = ""
smartsheet_client = smartsheet.Smartsheet(access_token)

def import_and_update_sheet(file, header_row_index, primary_column_index):
    # Import Excel file
    result = smartsheet_client.Sheets.import_xlsx_sheet(file, sheet_name=None, header_row_index=None, primary_column_index=primary_column_index)
    print(result)

    # Get sheet and header row
    sheet_id = result.data.id
    sheet = smartsheet_client.Sheets.get_sheet(sheet_id)
    header_row = sheet.rows[header_row_index]

    # Update columns
    for cell, column in zip(header_row.cells, sheet.columns):
        print(cell.value, column.id, column.index, column.type)
        column_spec = smartsheet.models.Column({
            'title': cell.value,
            'type': column.type,
            'index': column.index,
            'width': 150
        })
        # Update column
        response = smartsheet_client.Sheets.update_column(
            sheet_id,       # sheet_id
            column.id,      # column_id
            column_spec)

    # Remove header row
    row_ids_to_remove = []
    for row in sheet.rows:
        if row.row_number <= header_row.row_number:
            row_ids_to_remove.append(row.id)
    smartsheet_client.Sheets.delete_rows(sheet_id, row_ids_to_remove)

# Usage example
file = '/content/IndentedExcelRowsHeaderAt2.xlsx'
header_row_index = 2
primary_column_index = 0

import_and_update_sheet(file, header_row_index, primary_column_index)