I am trying to write a function that will do the following:
As an advisor, I keep track of every communication in my google spreadsheet (to fasten it up, I created a form, so data comes from a form). And, my supervisor also requesting from me to log every communication/meeting on my google calendar as well. So, instead of trying to put these data in two different places, I wanted to get benefit of technology, and by just filling out my log form, same data will also go into my calendar and into my spreadsheet as well.
This is my google spreadsheet(I created this fake one for you):
https://docs.google.com/spreadsheets/d/1BnFep6hG9Ee8wbGGw0Ne-vkAaonSVHtmQZSKRqp57nA/edit#gid=0
The columns {'Timestamp', 'Client Name', 'Activity', 'When did it take place?', 'How long did it take?'} are coming from the Google Form. I then just add the time in 'How long did it take?' part to 'when did it take' and finding the End Time.
The column process is a check cell that I created for function. it is basically doing =if(ISBLANK(B4),"","new") , so if cell value is new, then function in the google script will run and add the data into my calendar, and after that it will change the cell value to "added to calendar", so it won't add the data into my calendar again and again (which I somehow accidentally did yesterday :)
This is all I need, if you could help me out with this, I really appreciate a lot.
First Attempt:
And here is my second attemtp:
I have tried to come up with these attempts after I review these topic/question:
http://stackoverflow.com/questions/15981242/google-script-replace-certain-cell-values-in-a-column
Also, first attempt kind of works, it adds the data in my calendar, and it replaces the cell value to "added" of the 1st row, but then it doesn't do this for the rest of the rows. Plus, I actually put an if statement just like in the 2nd attempt, but even if I change the cell value to something else, it still runs the function and adds the date in my calendar.
I removed the pictures and replaced them with the codes.