What I’m trying to do:
I’m trying to retrieve the data from database and then create a JSON Array to send it to Google Gantt.
What I’ve tried and what’s not working:
I’m having propably problems with the conversion of python date to JSON date.
Really I’m out of ideas how to do it without using quotes in python.
It’s directly related to @stucork answer on other topic
Clone link:
FYI, the link to the other topic was a bad link (apparently truncated), so it doesn’t go anywhere.
In fact, JSON does not define a date type. That means, if you have a Python date value – or any other value that JSON does not directly support – then you’ll need to convert it to whatever JSON type (and format!) Google Gantt is expecting to receive.
I’m not familiar with Google’s offerings. So, the next question is, what type, and format, is Google Gantt expecting, for these values?
Yes, but without the quotes. I can’t fogure it out. How to convert the python date to this withou quotes
I still don’t know what “this” looks like!
Edit: Or is supposed to look like. An example would help us help you.
example of valid data from docs:
data.addRows([
['Research', 'Find sources',
new Date(2015, 0, 1), new Date(2015, 0, 5), null, 100, null],
['Write', 'Write paper',
null, new Date(2015, 0, 9), daysToMilliseconds(3), 25, 'Research,Outline'],
['Cite', 'Create bibliography',
null, new Date(2015, 0, 7), daysToMilliseconds(1), 20, 'Research'],
['Complete', 'Hand in paper',
null, new Date(2015, 0, 10), daysToMilliseconds(1), 0, 'Cite,Write'],
['Outline', 'Outline paper',
null, new Date(2015, 0, 6), daysToMilliseconds(1), 100, 'Research']
]);
Error:
ExternalError: Error: Argument given to addRows must be either a number or an array
Print result from the app:
[["St001", "St001 Sample Test 01", "Sample Test 01", "new Date(2023, 5, 8)", "new Date(2023, 7, 23)", null, 0.0, null], ["St001_002", "St001_002 TestKR", "test@gmail.com", "new Date(2023, 6, 21)", null, 691200000, 4.166666666666666, "St001"], ["St001_003", "St001_003 TestData", "n.a.", "new Date(2023, 6, 31)", null, 1296000000, 13.333333333333334, "St001"], ["St001_004", "St001_004 Test", "test2@kr1.de, kr2213@kr.de, test1@gmx.de, test2@kr1.de, kr2213@kr.de, test1@gmx.de, test@gmail.com", "new Date(2023, 7, 6)", null, 604800000, 90, "St001"], ["St001_005", "St001_005 Test Panels", "test1@gmx.de, test@gmail.com", "new Date(2023, 7, 7)", null, 604800000, 90, "St001"], ["St002", "St002 Test import 02", "Test import 02", "new Date(2023, 5, 9)", "new Date(2023, 9, 9)", null, 0, null]]
The new, null, and the trailing ; all make it clear that the example code is written in Javascript, not Python.
I’m assuming that you are trying to write the Python equivalent. May we see your Python equivalent?
What should this read?
I’m trying to convert python to JS equivalent to add it to the function drawing Gantt.
That would be that, but as valid accepted format for the drawing function
[["St001", "St001 Sample Test 01", "Sample Test 01", "new Date(2023, 5, 8)", "new Da
I don’t have any idea how to convert datetime.date from python to new Date(year, month, day) in JS. I’ve managed to make only a string representation of that, but it’s string not date
I should have been clearer.
You have the JS code example. I’m assuming that you need the equivalent Python code. You’ve shown us what the broken data looks like, as produced by the current broken Python code. To make any progress, we need to see
That is an example from Google docs. That is what the function expects to get.
My broken code on server side:
@anvil.server.callable
def multi_study_gantt_show(include_tasks, include_users, include_completed, ids):
"""generate gantt of multiple studies"""
#data.addColumn('string', 'Task ID'); #study_id not visible link
#data.addColumn('string', 'Task Name'); #visible name
#data.addColumn('string', 'Resource'); #visible ressource
#data.addColumn('date', 'Start Date'); #start
#data.addColumn('date', 'End Date'); #end
#data.addColumn('number', 'Duration'); #optional
#data.addColumn('number', 'Percent Complete'); #% of ceompletion
#data.addColumn('string', 'Dependencies'); #links as dependencies
#['2014Spring', 'Spring 2014', 'spring', new Date(2014, 2, 22), new Date(2014, 5, 20), null, 100, null]
def daysToMilliseconds(days):
return days * 24 * 60 * 60 * 1000
data_list = []#collects the data
low_date = None #collect the lowest date
high_date = None #collect the highest date
#extract ids from a longer strings
#example ['St001 Study BAC', 'St00b DAHEW']
# Create a pattern to match "St" followed by one or more digits
pattern = re.compile(r'St\d+')
# Extract the matching part from each string in the list
extracted_ids = [match.group(0) for string in ids for match in re.finditer(pattern, string)]
if include_completed == True:
include = ''
else:#exclude completed or cancelled
include = 'completed', 'cancelled'
retreived_data_studies = app_tables.projects.search(q.fetch_only('study_name', 'study_id', 'planned_start', 'planned_end', 'number_of_tasks', 'completed_tasks', 'client'),
study_id=q.any_of(*extracted_ids), study_status=q.none_of(include))
#build a new list
for row in retreived_data_studies:
#set columns
column_study_id = row['study_id']
column_task_name = f"{row['study_id']} {row['study_name']}"
column_ressource = row['client']
column_start = row['planned_start']
column_end = row['planned_end']
column_duration = None
#completion
number_of_tasks = row['number_of_tasks']
completed_tasks = row['completed_tasks']
# Calculate the percentage
if number_of_tasks > 0:
percentage = (completed_tasks / number_of_tasks) * 100
else:
percentage = 0 # To avoid division by zero if number_of_tasks is 0
column_completion = percentage
columne_dependencies = None#study is not connected to something
# Append the data as a list
data_list.append([
column_study_id,
column_task_name,
column_ressource,
column_start,
column_end,
column_duration,
column_completion,
columne_dependencies
])
#assign lowest date
if low_date is None or row['planned_start'] < low_date:
low_date = row['planned_start']
#assign highest date
if high_date is None or row['planned_end'] > high_date:
high_date = row['planned_end']
#include tasks OPTIONAL
if include_tasks == True:
data_tasks = app_tables.task_manager.search(q.fetch_only('study_id', 'task_name', 'task_id', 'task_type', 'effort_hours', 'start_date', 'total_objects', 'completed_objects', 'status', assigned_users=q.fetch_only('email')),study_id=row['study_id'])
for task in data_tasks:
column_sub_study_id = task['task_id']
column_sub_task_name = f"{task['task_id']} {task['task_name']}"
#include users?
if include_users:
assigned_users = task['assigned_users']
# Check if assigned_users is None or empty
if not assigned_users:
column_sub_resource = "n.a."
else:
# If it's a list of users, join their emails with a comma and space
if isinstance(assigned_users, list):
user_emails = [user.get('email', 'n.a.') for user in assigned_users]
column_sub_resource = ", ".join(user_emails)
else:
# If it's a single user dictionary, get their email
column_sub_resource = assigned_users.get('email', 'n.a.')
else:
column_sub_resource = None
column_sub_start = task['start_date']
column_sub_end = None#based on duration
# Calculate column_duration based on task_hours
task_hours = task['effort_hours'] # Replace with your actual data field
hours_per_day = 8 # 8 hours per day
column_sub_duration = math.ceil(task_hours / hours_per_day)
# Convert column_sub_duration to the correct format, without quotes
column_sub_duration = daysToMilliseconds(column_sub_duration)
# Assuming you have 'number_of_objects' and 'completed_objects' in your task data
number_of_objects = task['total_objects']
completed_objects = task['completed_objects']
task_status = task['status']
# Calculate the percentage without exceeding 90%
if number_of_objects > 0:
percentage = min((completed_objects / number_of_objects) * 100, 90)
else:
percentage = 0 # To avoid division by zero if number_of_objects is 0
# Add the additional 10% if task is completed
if task_status == 'Completed':
percentage += 10
column_sub_completion = percentage
columne_sub_dependencies = task['study_id']#assign to study
# Append the task data as a list
data_list.append([
column_sub_study_id,
column_sub_task_name,
column_sub_resource,
column_sub_start, # Plain JavaScript code
column_sub_end, # Plain JavaScript code
column_sub_duration, # Plain JavaScript code
column_sub_completion,
columne_sub_dependencies
])
# Custom JSON encoder to convert datetime.date to JavaScript Date
class DateEncoder(json.JSONEncoder):
def default(self, obj):
if isinstance(obj, datetime.date):
# Convert datetime.date to JavaScript Date object
return {"__date__": True, "year": obj.year, "month": obj.month - 1, "day": obj.day}
return super().default(obj)
# Encode the array using the custom encoder
data_json = json.dumps(data_list, cls=DateEncoder)
print (data_json)
return low_date, high_date, data_json
Ok, since this is all javascript, but what we want is the json string example of what this javascript produces, I put it into JavaScript Playground and got:
[["Research","Find sources","2015-01-01T05:00:00.000Z","2015-01-05T05:00:00.000Z",null,100,null],["Write","Write paper",null,"2015-01-09T05:00:00.000Z",259200000,25,"Research,Outline"],["Cite","Create bibliography",null,"2015-01-07T05:00:00.000Z",86400000,20,"Research"],["Complete","Hand in paper",null,"2015-01-10T05:00:00.000Z",86400000,0,"Cite,Write"],["Outline","Outline paper",null,"2015-01-06T05:00:00.000Z",86400000,100,"Research"]]
"2015-01-06T05:00:00.000Z"
This looks to me like ISO 8601 date format, you would have to mess with timezones in python to get the same format, since it looks like my browser just implicitly attached my current timezone when calling the javascript Date() function. 
…if you had a timezone-aware datetime object in python you should be able to use .isoformat() on it to get a string that looks like this.
It looks like it’s coming out of an Anvil table date column, so it will be timezone-aware.
I’ve checked the docs about date in Google:
'MMM DD, YYYY' or 'DD MMM, YYYY' (Example: new Date('Jan 1, 2015') or new Date('1 Jan, 2015'))'YYYY-MM-DD' (Example: new Date('2015-01-01'))Yes, the data comes from the anvil table date column.
.isoformat()
gives me that output:
“2023-06-08”
It’s still not the right format and I’m getting this error:
ExternalError: Error: Argument given to addRows must be either a number or an array
It’s says you should use a date constructor:
### Dates Using the Date Constructor
To create a new Date object, you call the Date() constructor with the new keyword, with arguments to specify components of the date. These arguments take the form of several numbers corresponding to the different properties of your date.
new Date(Year, Month, Day, Hours, Minutes, Seconds, Milliseconds)
Cast your date into a datetime object. The date column uses a date object I think?
example:
Yes, it is date object. I can use the string date but need to insert somehow the date constructor in my JS Array
new Date(string_date)
I believe that you’re taking the example code too literally. The list contains values, computed by the shown code, not a copy of the actual code.
So, what appears in the list is the result of invoking Javascript’s date constructor: a constructed Date object.
You’re running Python code, so you should be using Python code that produces the equivalent values.
I don’t know javascript, so I can’t help you there.
You are either replicating the same output that javascript does (JSON, as created entirely by python), or formatting the data into objects that javascript will read (then convert to JSON? 
) .
I think a clear direction needs to be chosen one way or the other, since you cant mix them together?
@p.colbert and I seem to see this as an ETL problem, not a javascript problem I guess? 
My data is in python, but the Google want JS Array. So the only option left is to convert it and provide in the format that he accepts.
If you’re calling an Anvil-provided Python API for Google Gantt, then the API will convert the list for you.
If you’re calling a Javascript API, then look here: Using JavaScript. Again, Anvil will convert the Python list into an equivalent Javascript list for you.
Thanks for the docs. I did it and yes Google now see the array that I pass as python and the problem with Dates persist.
Already tried few different formats that are specified in google docs. Even conversion to milliseconds.
Index 3 is a first date column. It still want from me the construction new Date(date)
ExternalError: Error: Type mismatch. Value 2023-06-08 00:00:00+00:00 does not match type date in column index 3
ExternalError: Error: Type mismatch. Value 2023-06-08 does not match type date in column index 3
Even when the constructor was added as a string “new Date(date)”
Data:
['St001', 'St001 Sample Test 01', 'Sample Test 01', "new Date('2023-06-08')", "new Date('2023-08-23')", None, 0.0, None]
ExternalError: Error: Type mismatch. Value new Date('2023-06-08') does not match type date in column index 3
or milliseconds
['St001', 'St001 Sample Test 01', 'Sample Test 01', 'new Date(1686182400000)', "new Date('2023-08-23')", None, 0.0, None]
ExternalError: Error: Type mismatch. Value new Date(1686182400000) does not match type date in column index 3