Edit the date format the Flic button provides

[WinstonSmith]

Hello,

Excellent product, really enjoying using it (Them even)!

However I have a small problem, I have an If This Then That recipe that when the recipe receives an event from a particular Flic button it adds a row to the bottom of a Google spreadsheet with the time it was clicked. All great and groovy so far.

However, is there a way to change the format of the date and time that the Flick button provides? Trying to do sums in a spreadsheet on something like "August 29, 2016 at 08:30AM" is really difficult, so I was wondering if it was possible to either split it up into two cells so one for the date and one for the time, or just change it altogether?

Could really do with this solving ASAP, I'm using it to track when my personal assistants come and go so that I can calculate thier hours at the end of the week. To be clear, the Flic ALWAYS recognises when each of the girls has pressed the button; one of them clicks once to check-in and the other one clicks and holds. My only issue was with the date and time format.

Thanks in advance

Winston

[timvoortaal]

@WinstonSmith

Hi there.

I'm really curious how you managed to get the datestamp into google sheets.
ID, press type, lat and long are all no problem but, the timestamp never works.

[anton]

Actually I take this back.. I tried it with IFTTT now and apparently the up onEdit() trigger only fires on user input and not on submission from third party apps. So this is not a good solution for you. If you want to do some programming on your own then you can take a look at our Flic SDK's for iOS/Android/Mac/Linux/PC and build it yourself, but that is perhaps taking it too far

[anton]

@WinstonSmith Well I don't know of a good solution at the moment.. If you are willing to do some "hacking" of your own then you could try and add a script to your spreadsheet that automatically adds a date once a new row is inserted into the document. This is maybe not ideal, but it might work for you. How I would do that is probably to add another column to the spreadsheet and then let it be empty when you append rows through IFTTT. Then in the document go to Tools -> Script editor... and in the script file that is created remove the auto generated script and instead enter the following:

function onEdit(e) {
  var columnIndex = 1; // Index 1 is the first column (column A)
  
  var activeSheet = e.source.getActiveSheet();
  var range = activeSheet.getRange(1, columnIndex, activeSheet.getMaxRows(), 1);
  var firstColumn = range.getValues();
    for (var i = 0; i < firstColumn.length; i++) {
      if (firstColumn[i][0] == "") {
         firstColumn[i][0] = new Date();
      }
    }
  range.setValues(firstColumn);
}

Change the columnIndex parameter on the second line to correspond to the index of the column you want the date to be added to. This script will input the current time and date to any empty row in that column. If you try this, then please try it on a non-important document to make sure that it works for you before adding it to the real document. I have only tried this within goggle spreadsheets itself and not through IFTTT.

[WinstonSmith]

@anton Hi, thanks for the reply. I'm using the iOS app. Is there any other way you can think that I might be able to do this?

Thanks

Winston

[anton]

@WinstonSmith Hello, I have added a ticket about this in our internal bug system. We will look, but at the moment our back-log is quite large so I can not promise when it will be solved. One question though, are you using the app for Android or iOS?

/Anton