Update Googlesheet cell with timestamp from Python

Question

I'm attempting to update a cell in a google sheet with the current date/time of my machine using python 3.6.5. I'm using gspread to connect to the google sheet.

If I do the following it will give the date/time that I'm looking to put into google sheets:

import datetime
print(datetime.datetime.now())
2018-09-10 10:50:38.171795

I'm struggling to figure out how to get that output to be printed into a cell in google sheets. Here's my setup and then the last line is what I'm trying to figure out:

import datetime
import gspread
from oauth2client.service_account import ServiceAccountCredentials

#connect to google sheets through API
json_key = 'work.json'
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_key, scope)
client = gspread.authorize(credentials)

#define googlesheet
rsheet = client.open_by_url('https://docs.google.com/spreadsheets/d/randomgooglesheetid')

#define the correct worksheet, index - 0 is the first sheet, 1 is the second...
engwsr = rsheet.get_worksheet(0)

engwsr.update_acell('O1' , print(datetime.datetime.now()))

This last line just prints the datetime into python and doesn't update anything in the google sheet. My goal is to have '2018-09-10 10:50:38.171795' printed into cell O1.

Is there some better way to do this? The datetime format doesn't have to be exactly like that, just something easily readable with the date and time.

Gagarin

Answer 1

Instead of

engwsr.update_acell('O1' , print(datetime.datetime.now()))

put

engwsr.update_acell('O1' , datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f'))

print() has no return value, that is why you do not see anything in your sheet.

Enterogastrone

Answer 2

You could use this function, it worked for me:

Imports:

import gspread
import datetime

Lets have a variable now as datetime object:

now = datetime.datetime.now()

then pass now to the following function as string.

wb.values_update(
    'sheet1!A2',
    params={
        'valueInputOption': 'USER_ENTERED'
    },
    body={
        'values': [[str(now)]]
    }
)

This way your entered datetime will be recognized by google sheet as if it was entered by a person. If you want to know more about how to use gspread follow this link.

Entering multiple values could be done as well by replacing [[str(now)]]

with:

[[str(now) ,'b','c','d']]

Bobbiebobbin