Upload in Batch Python API?
Is there any way of uploading data into Smartsheet in batches?
Still fairly new playing around with Python and Smartsheet's API.
The full script have works, its just I have 10000+ rows I want to upload.
The script below is what I use to delete the information from the sheet in batches.
# Get the rows from the sheet
sheet = smartsheet_client.Sheets.get_sheet(sheet_id)
rows = sheet.rows
# Collect the row IDs
row_ids = [row.id for row in rows]
# Maximum number of rows to delete in a single request
batch_size = 350 # Adjust this as needed
# Delete rows in batches with status bar
for i in tqdm(range(0, len(row_ids), batch_size), desc="Deleting rows"):
batch_ids = row_ids[i:i+batch_size]
smartsheet_client.Sheets.delete_rows(sheet_id, batch_ids, ignore_rows_not_found=True)
This is what I currently have uploading the data. Its getting the data from a csv.
# Create cells for the new row
cells = []
for column_id, cell_value in zip(column_ids, row_data):
cell = smartsheet_client.models.Cell({
'column_id': int(column_id), # Convert string to integer
'object_value': cell_value
})
cells.append(cell)
# Create a new row with the cells
row = smartsheet_client.models.Row({'cells': cells})
# Add the row to the sheet
smartsheet_client.Sheets.add_rows(sheet_id, [row])
Answers
-
Hi @aagundez
For smaller CSV files such as 10,000 rows and six columns, you can use Smartsheet python SDK's import_csv() method.
Example code:
result = smartsheet_client.Sheets.import_csv_sheet(
path,
sheet_name="New Sheet Name",
header_row_index=0,
primary_column_index=4
)
If you want to import a large CSV file, for example, a file with more than 13,187 rows and 17 columns, to Smartsheet, you can take these steps as the above import_csv_sheet() gives an error, "Server timeout exceeded. Request has failed.";
Steps:
1. Use the CSV module to load the data from the CSV file into a list of rows.
2. Sets the column names based on the header row and creates a new Smartsheet with the specified or inferred sheet name and column structure.
3. Adds the rows from the CSV file to the newly created sheet
Example code:
https://gist.github.com/jmyzk/5c5229128b11ef3ec3ec4ac2383c5d15
