Date/time output has single quote in front of variable in Google Sheet

Question

I have some code to export some output to a Google Sheet. The command I am using for that is:

row = [timestamp, float(temperature), float(humidity), current_temperature, current_humidity, current_pressure, weather_description]
sheet.insert_row(row, index)

The issue is that the timestamp variable is adding a single quote in front of itself when it is inserted into the sheet, but when using print(timestamp), this quote is not there.

This makes it difficult because I cannot make any graphs with that quote in front of the output. The commands I am using for time are as follows:

now = datetime.now()
timestamp = now.strftime("%m/%d/%Y%l:%M:%S %p")

How can I get a timestamp without a single quote in front?

Answer 1

This is usually to indicate that the input is a string. I recommend to just insert the date using datetime.now() in this way google sheets will recognize it as a date rather than a string.

timestamp = datetime.now()
row = [timestamp,float(temperature),float(humidity),current_temperature,current_humidity,current_pressure,weather_description]
sheet.insert_row(row, index)

Edit:

If you want a special date format in your sheet, you will need to use google sheets API and format the column accordingly. I recommend using the library gspread is really useful when it comes to google-sheets formatting and easy to use: gspread docs

There is also another a special library to use gspread with pandas, I find it really useful as well: gspread-pandas

Answer 2

Try sheet.insert_row(row, index, value_input_option='USER_ENTERED').

By default, value_input_option will be RAW:

The values the user has entered will not be parsed and will be stored as-is.

Use USER_ENTERED:

The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI.

https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption