I have these 2 columns in my sheet: A and B.

A contains dates in format day.month.year. B contains hours in format hh:mm. I usually concatenate the values of these 2 columns by using this formula:

=concatenate((text(A3;"mm.dd.yyyy")&" "&text(B3;"hh:mm")))

I use the same formula to concatenate the values of 2 other cells: C (filled with dates!) and D (filled with hours!). The exact formula in this case is:

=concatenate((text(C3;"mm.dd.yyyy")&" "&text(D3;"hh:mm")))

The whole purpose of this little exercise is to get the dates and hours in the format that is required to create events in my Google Calendar. I am currently using it in my calendar event creation script with the method setFormulaR1C1(formula), but I am not sure if that´s a really good idea. So, please tell me:

1. How would an apps script equivalent of my formula look like?
2. And is there a way to write that apps script equivalent of my formula as a callback function?

Thank you so much in advance!

CodePudding user response：

How would an apps script equivalent of my formula look like?

Given that the cells already display the date and time in the format you need, you can simply use this:

const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
const datetimeString = 
  sheet.getRange('A3').getDisplayValue()
    ' '
    sheet.getRange('B3').getDisplayValue();

If the cells do not display the date and time in the required format, you will need to use Utilities.formatDate() and Spreadsheet.getSpreadsheetTimeZone(). There are some complications when interpreting spreadsheet time values in Apps Script. Refer to the recipe at Google Script when taking time from sheets adds 1 minute to avoid surprises.

To get the same with a spreadsheet formula more easily, use this:

=trim(A3) & " " & trim(B3)