I'm trying to convert a date in a Google Spreadsheet column from 3/3/2023 to Friday March 3, 2023 with pygsheets.
The following code:
client = pygsheets.authorize(service_account_file="credentials.json")
test_sheet = client.open(titles[0])
test_worksheets = test_sheet.worksheets()
active = test_worksheets[0]
model_cell = pygsheets.Cell("A1")
model_cell.set_text_format("fontSize",18)
model_cell.set_vertical_alignment(pygsheets.VerticalAlignment.MIDDLE)
model_cell.set_number_format(pygsheets.FormatType.DATE, 'dddd+ mmmm yyy')
pygsheets.DataRange('A2', 'A', worksheet=active).apply_format(model_cell)
successfully changes the fontSize and VerticalAlignment attributes but does not change the date format. What is wrong with the code?
UPDATE:
It seems there are two things at play here. First, my date format string isn't what I wanted and so it's possible that gsheets wasn't able to interpret it and just ignored. I'm skeptical that's the case but it's possible. The format string I need to use is 'dddd", "mmmm" "d", "yyyy'.
Second and more importantly, I noticed that after I move the date values from one column to another there is a single quote (or possibly a tick mark) at the start of the date string. I remove this quote and the format changes.
Seems like pygsheets is adding a tick mark at the beginning of non-numerical dates (for example 3/3/2023) i'm guessing to preserve the original formatting. But when you call the batch updater it doesn't remove the tick mark.
I'm not entirely sure how to get around this but at least I know what I need to get around now.
This almost seems like it's a bug but it's maybe more of a feature request or at least an update to the documentation.
What I didn't mention in my question was that before I tried to modify the format of the column, I moved the values from another column into column A. In the process, it seems, pygsheets adds a single quote or tick mark to the beginning of the date strings - presumably to maintain the original formatting.
Unfortunately this single quote seems to get in the way of gsheets modifying the string into a Date format. When I remove the tick mark/single quote, the formatting started to show up. So, for my issue I just modified the Date format before moving the values.