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

Options
jzinna
jzinna
edited 07/18/22 in API & Developers

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.

Answers

  • Genevieve P.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now