Export All Google Tasks then Import into TickTick for All tasks : going back years!

Problem:

Google Task import into TickTick or GTasks only imports GTasks that are active and if Completed, then only 1 year old.

If you want to import very old Completed tasks into TickTick, there is no way to do this with the TickTick nor Google GTasks.

Solution:

Pre Req’s:

Get the Google Tasks Export Format:

https://tasks-backup.appspot.com/

Get the TickTick Import File Format:

See  https://help.ticktick.com/forum/topic/348273/import-list-items-from-excel

Also, run an export to see the EXACT format, there is heade and footer stuff

Run a export and save as a SampleTickTickExport.csv

See Figure 2 to compare TickTick and Google tasks csv format.

1. Export ALL tasks from Google Tasks to .csv including tasks up to 5 years old. using https://tasks-backup.appspot.com/ (check all the check boxes for hidden/deleted)

tasks_import_export_[email]_2018-05-24.csv

2. import that .csv into a SQL table, then transform the data to what TickTick expects.

Import GoogleTasks.csv  into SQL table. : [tasks_import_export_f]

Use SQL (attached file TaskImportExport.sql) to clean up data. Step 1 in sql file

Use SQL to create a new table “Staging” [tasks_import_export_d] Step 2 in sql file

3. Export staging data (see #2 below in SQL) to  a csv file using Results Save to file, save the csv name Staging_GTasks.csv 

4. Because TickTick wants unix style LF, you must supply a LF at end of each line , NOT CRLF

Open in Notepad++, replace CRLF with LF for (very easy in Notepad++)

5. Open Staging_GTasks.csv  in textpad & open SampleTickTickExport.csv

paste the contents from  Staging_GTasks.csv   into SampleTickTickExport.csv  in between header and footer.

6. import into Tick Tick using back utility (very nice!)

https://www.ticktick.com/#settings/backup

7. Done!

8. Someday soon automate this! perhaps 2-3days of code we could make a web app to create the TickTick file, or use this manual proc,

here is API that may be a start:

FAQ:

https://guide.ticktick.com/does_ticktick_have_an_api.html

Code:

https://docs.google.com/document/d/1zo0JdIWnQWi-D0v7xikknEnsLj4RmrzOuqzScdR_tm8/edit#heading=h.vmuori4i7puz

https://gist.github.com/jackinside/22dd1ed1011403646d6a

9. I may just change my mind buz:

https://blog.hubspot.com/marketing/best-to-do-list-apps-tools

Why pay when I get possibly all I need in Wunderlist?

Figure 1:

Figure 2:

Figure 3 SQL:

use [myNGC_STG_Custom_PCard]
go

-- -1) Clean Up

--select *  FROM [dbo].[tasks_import_export_f] where len([due])= 0 and len([completed]) =0
--update [dbo].[tasks_import_export_f] set [due] = '2018-04-29' where len([due])= 0 and len([completed]) =0
--select *  FROM [dbo].[tasks_import_export_f] where len([due])= 0 and len([completed]) =0
--update [dbo].[tasks_import_export_f] set [due] = '2018-04-29' where len([due])= 0 and len([completed]) =0

----update [dbo].[tasks_import_export_f] set [due] = [due] + 'T04:00:00+0000' where len([due])> 0
--update [dbo].[tasks_import_export_f] set [completed] = left([completed], 10) where len([completed])> 0
--update [dbo].[tasks_import_export_f] set [completed] = [completed] + 'T04:00:00+0000' where len([completed])> 0

--2) export to csv, save to file, replace CRLF with LF (Notpad++)
set nocount on

SELECT '"' +[tasklist_name]+ '"' "List Name"
       ,'"' +[title]+ '"' "Title"
       ,'"' +replace(dbo.[udfTrimInvalidChars](ltrim(rtrim([notes]))), '\n' , CHAR(13))+ '"'  "Content"
       --,replace(dbo.[udfTrimInvalidChars](ltrim(rtrim([notes]))), '\n' , CHAR(10) )  "Content2"
      
       ,'"' +'N' + '"' "Is Checklist"
       ,'"' +[due]+ '"' "Start Date"
       ,'"' +[due]+ '"' "Due Date"
       ,'"' +''+ '"' "Reminder"
       ,'"' +''+ '"' "Repeat"
       ,'"' + '0'+ '"' "Priority"
       ,'"' +case when len([completed]) > 0 Then '2' Else '0' End + '"' "Status"
       ,'"' +case when len(due) =0 then [completed] Else [due] End + '"' "Created Time"
       ,'"' +[completed]+ '"' "Completed Time"
       ,'"' +'-1099511627776' + '"' "Order"
       ,'"' +'America/New_York'+ '"' "Timezone"
       ,'"' +'' + '"' "Is All Day"
   FROM [dbo].[tasks_import_export_f]

CREATE FUNCTION [dbo].[udfTrimInvalidChars](@String VARCHAR(MAX))

RETURNS VARCHAR(MAX)
BEGIN
     Declare  @Result varchar(max)
    
     Set @Result = ltrim(rtrim(Isnull(@String, '')))
    
     Set @Result =  replace(replace(replace(replace(replace(replace(replace(@Result, '''', ''), '"', ''), '  ',  ' '), char(160), ''), CHAR (13) + CHAR (10) , ''), CHAR (10), ''), CHAR (9), '')
     Set @Result = case when len(@Result) = 0 then null else ltrim(rtrim(@Result)) End
 
 
     return dbo.[udfTrim](@Result)
END

 
 
 

 

CREATE FUNCTION [dbo].[udfTrim] (

@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS

BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE '[' + @trimchars + ']%' SET @str = SUBSTRING(@str, PATINDEX('%[^' + @trimchars + ']%', @str), 8000)
RETURN ltrim(rtrim(@str))
END

--Refs:
--https://www.cnet.com/how-to/how-to-import-and-export-in-google-tasks/
--https://productforums.google.com/forum/#!topic/calendar/pSmEFZPJG50
--https://www.technorms.com/24082/export-gmail-tasks-google-tasks-backup
--https://tasks-backup.appspot.com/progress--https://help.ticktick.com/forum/topic/348273/import-list-items-from-excel
--https://www.ticktick.com/#settings/backup