Format dates in Google sheet while writing through Datatable

Hi Team,

I am reading data from excel of which one Column is ‘Joining Date’ formatted as ‘dd-mmm-yyyy’. I have stored them in data table named ‘dtUserNotLoggedInDump’ in Uipath and while writing the data table ‘dtUserNotLoggedInDump’ to Google sheet by using DateTime.ParseExact For each row method it is taking much time because I have 3-4 thousand lines of data and For Each row writes the data row-by-row. I have also tried to check the ‘PreserveFormat’ box while reading the excel range but, even this takes time.

Is there any way I can change the format of entire column while writing from Excel to Google sheets (“dd-MMM-yyyy”) . It would be very useful. Thanks.

I have shared the code used in DateTime.ParseExact For each row method below for your reference along with the snapshot.

  1. Read Excel range and store in data table ‘dtUserNotLoggedInDump’
  2. Use For Each Row inside the Gsuite Application scope
  3. Assign str variable
    JoiningDate = DateTime.ParseExact(CurrentRow(“Joining Date”).ToString,“MM/dd/yyyy hh:mm:ss”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MMM-yyyy”)
  4. Write above variable value back to current row of Data table
    CurrentRow(“Joining Date”) = JoiningDate
  5. Google sheet Write range

Please help. I am very much new to Uipath. Thanks in advance.!



Snap3

Hi @Sujit_Pavaskar

Welcome to UiPath Forum

Thank you for explaining your issue in detail.

I have not used Gsuite ApplicationScope but have updated datetime values like your scenario using Invoke code Method.

Attached the sample. Please check if it helps you.

UpdateDateTimeValue.zip (8.9 KB)

Thanks,
Boopathi.

Hi Boopathi,

Thanks a lot for your prompt response and solution. :grinning: Your code is working for me where excel are dates present i.e. (Date column → cells are not blank/missing) and also, I am able to write dates in correct format to Google sheets.

But it throws error “Invoke code - From Date: Exception has been thrown by the target of an invocation.” if it finds blank cells. Could you please modify the code to handle blank cells where dates are missing in some cells.

Attached snapshot for your reference where you can see that code is working perfectly fine for first three rows marked under green box.

Please help. Thanks in advance! :pray:

hi @Sujit_Pavaskar

Glad the attached xaml works for you. Modified the code to write empty date column as string.empty in column or you can give any default value you want.

UpdateDateTimeValue_1.zip (9.0 KB)

If it helped you, you can mark this thread as solution.

Thanks,

1 Like

Hi! @Boopathi.M

Good morning! Thank you very much for the modified code as per request. It is working perfectly fine. I am marking your last comment as Solution. It will definitely help others as well.

Thanks again. Have a nice day ahead. :slight_smile:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.