Python API - How do I transfer a date-time value from a Pandas data frame to a new row?

jzinna

Hi! This is my first question in the forum, so please let me know if I'm doing something wrong.

I have some data in an excel csv file, and I have copied it into a Pandas data frame. One of the fields in the csv has this format: "3/17/2022 10:59:00 AM" and when transferred to the data frame it shows '3/17/2022 10:59'. So I convert it to what I think is the right Smartsheet format with this line, 'Created' being the column name:

data_frame['Created'] = pd.to_datetime(data_frame['Created']).dt.strftime("%Y-%m-%dT%H:00:00Z") 

and ends up being '2022-03-17T10:00:00Z'.

When I create the smartsheet row using a loop and

new_row.cells.append({'column_id':column_ids[m],'objectValue':data_frame.loc[n][m],'strict':True})
smartsheet_client.Sheets.add_rows(SHEET_TO_MODIFY,new_row)

the field shows in smartsheet as '2022-03-17T10:00:00Z'. And if I use a formula like =TODAY() - [Created Date]@row, I get an error #INVALID OPERATION.

How can I get it right?

If I use "%Y-%m-%d" it does not work, I get 2022-03-17 but it still throws an error in the sheet.

If I do something like

new_row.cells.append({'column_id':7150106886596484,'value':"6/7/2022",'strict':False})

it works, but to do that I would need to split the loop and it may overcomplicate things.

Genevieve P.

Hi @jzinna

If I'm understanding you correctly, you can get the information into Smartsheet with this:

new_row.cells.append({'column_id':column_ids[m],'objectValue':data_frame.loc[n][m],'strict':True})
smartsheet_client.Sheets.add_rows(SHEET_TO_MODIFY,new_row)

But the information brought in is formatted as text ('2022-03-17T10:00:00Z') so the cell in Smartsheet stores it as text and therefore you can't use a formula such as =TODAY() - [Created Date]@row

What I would do in this instance is keep your code as-is, but set up a helper column in the sheet to translate your text to a date using the DATE function:

=DATE(yyyy, mm, dd)

We can grab these numbers from your string as long as they will always come in as 2022-03-17 or YYYY-MM-DD.

For the Year:

VALUE(LEFT([Created Date]@row, 4))

For the Month:

VALUE(MID([Created Date]@row, 6, 2))

For the Day:

VALUE(MID([Created Date]@row, 10, 2))

For a full formula:

=DATE(VALUE(LEFT([Created Date]@row, 4)), VALUE(MID([Created Date]@row, 6, 2)), VALUE(MID([Created Date]@row, 10, 2)))

Then once this is translated into a Date, you can use your formula:

=TODAY() - [Helper Date]@row

Or you could build it all into one formula:

=TODAY() - DATE(VALUE(LEFT([Created Date]@row, 4)), VALUE(MID([Created Date]@row, 6, 2)), VALUE(MID([Created Date]@row, 10, 2)))

See: MID Function / LEFT Function / VALUE Function / DATE Function

Cheers!

Genevieve