Google-sheets – Check from spreadsheet and add into Calendar, and place a controller not to repeat old added values

google sheetsgoogle-apps-scriptgoogle-calendar

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:

function CalendarUpdate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 2");
  var startRow = 2;  // First row of data to process
  var numRows = 10;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = ss.getRange(startRow, 10, numRows, 20)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  var event = CalendarApp.getDefaultCalendar();
  var j = 1;
  for(i in data){
    var row = data[i];
    var subject = row[2]+" with "+row[1];
    var startdate = row[8];
    var enddate = row[7];

    event.createEvent(subject, new Date(enddate), new Date(startdate));
    Logger.log('Event ID: ' + event.getId());

    var k = 2;
    var m = 10;
    var n = 1;
    var dataRange2 = ss.getRange(k, 7, m)
    var data2 = dataRange2.getValue();

    data2 = "Added";

    dataRange2.setValue(data2);
    k = k + 1;
    m = m + 1;
    n = n + 1;
  }

}

And here is my second attemtp:

function kalender() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 2");
  var lrow = ss.getLastRow();
  var rng = ss.getRange(2, 11, lrow - 1, 10);
  var data = rng.getValues();

  for(var i=0; i< data.length; i++){
    if(data[i][7] == "new"){

      var subject = data[i][2] + " with " + data[i][1];
      var startdate = data[i][8];
      var enddate = data[i][7];

      var event = CalendarApp.getDefaultCalendar();
      event.createEvent(subject, new Date(enddate), new Date(startdate));
      Logger.log('Event ID: ' + event.getId());

      data[i][7] = "added to calendar";
      rng.setValues(data);
    }
  }
}

I have tried to come up with these attempts after I review these topic/question:
https://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.

Best Answer

Here is how I come up with a solution to my this problem. In case in future anybody gets stuck in something like this, hope this solve his/her problem and gets job done. :)

Google Script:

    function kalender() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 3");
  var lrow = ss.getMaxRows();
  var lcol = ss.getMaxColumns();
  var rng = ss.getRange(1, 1, lrow, lcol);
  var data = rng.getValues();

  for(var i=1; i< 100; i++){
    if(data[i][5] == 'new'){

      var subject = data[i][2] + " with " + data[i][1];
      var startdate = data[i][3];
      var enddate = data[i][4];
   //   var enddate = new Date(addDays(startdate,times));
      var event = CalendarApp.getDefaultCalendar();
      event.createEvent(subject, startdate, enddate);
      Logger.log('Event ID: ' + event.getId());

      data[i][5] = "added to calendar";
      rng.setValues(data);
    }
  }
}