Google Sheets to Google Calendar Challenge

[Alexandra Medeiros]

Hello!

I am trying to create calendar events from a spreadsheet. I have been using app scripts but am struggling. It worked at first, but is now asking me for authorization of my google account but it's not working. It used to work, but only on the September month tab. I need the script to run through all the monthly tabs. I think I may need if/elseif statements but also haven't figured that out yet. 

Here's the code I've been using:

function createCalendarEvent() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var calendar = CalendarApp.getCalendarById('calendarID');

 

  var startRow = 2;  // First row of data to process - 2 exempts my header row

  var numRows = sheet.getLastRow();   // Number of rows to process

  var numColumns = sheet.getLastColumn();

 

  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);

  var data = dataRange.getValues();

 

  var complete = "Done";

 

  for (var i = 0; i < data.length; ++i) {

    var row = data[i];

    var action = row[0]; //action item

    var department = row[1];  //department

    var contact = row[2]; //department contact 

    var owner = row[3];  //owner

    var date = new Date(row[4]);  //renewal date

    var eventID = row[5]; //event marked Done

   

    if (sheet.getSheetName("September") & eventID != complete) {

      var currentCell = sheet.getRange(startRow + i, numColumns);

      calendar.createEvent(action, date, date, {

        description: department + '\r\n' + contact + '\r\n' + owner

      });

          currentCell.setValue(complete);

    }

  }

}

I have 3 questions:

1) How to remove the "authorization" issue?

2) How to make the code run through all the monthly tabs

3) Is it possible to change the color of events through the script so that all actions related to one department are coded a specific color?

Thank you in advance for your help!

[Laurie J. Nason]

Dear Alexandra,

In answer to your questions:

1. You should only be asked once for authorization, and once you grant it, when you open the sheet and run the script again, you shouldn’t be asked again if you have not added any more scopes in the code
2. For your September issue, you have a row that checks to see if the sheet name is September, if (sheet.getSheetName("September") & eventID != complete) { 

To fix this - you'll need to remove the first part of the if statement and only check if (eventID != complete) although, I am not sure where your complete variable is coming from - unless you mean that the value of the column F should say the word "complete" in which case you'll need to enclose it in single quotes e.g. 'complete' where you reference it.

      3. This is possible - however, you will need to modify your code a bit more - see this post for some more information on this. 

UNTESTED - but could do what you want:

        var myEvent = calendar.createEvent(action, date, date, {description: department + '\r\n' + contact + '\r\n' + owner });

        myEvent.colorId=11;

These are the color numbers I found for calendar events. There may be others, but I am not sure

Pale Blue	1
Blue	9
Pale Green	2
Green	10
Mauve	3
Pale Red	4
Yellow	5
Orange	6
Cyan	7
Gray	8
Red	11

Laurie

------ Original Message ------

From "Alexandra Medeiros" <alexandra...@providenceschools.org>

To "Google Apps Script Community" <google-apps-sc...@googlegroups.com>

Date 31/08/2022 21:39:54

Subject [Apps-Script] Google Sheets to Google Calendar Challenge

*****This information may be confidential and/or privileged.   Use of this information by anyone other than the intended recipient is prohibited.   If you received this in error, please inform the sender and remove any record of this message.*****

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/34da786f-cf92-40cf-83a6-c21ba8d0a553n%40googlegroups.com.

[Arne Ballegeer]

Maybe this can help to loop through the monthly tabs and get the name?

function allSheetsInSpreadsheet() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheets = ss.getSheets();

for (let sheet in sheets) {

const sheetName = sheets[sheet].getName();

console.log(sheetName);

}

}

grt

Arne

Op donderdag 1 september 2022 om 13:09:37 UTC+2 schreef laurie...@thekaustschool.org:

[Alexandra Medeiros]

Thanks for the help!

I figured out how to make it work! But I'm stuck on a new issue. When I run my script, it recodes eventID for each row instead of just the ones outlined by the if statement condition. Can anyone support?

Here's my script (I split them in two because it wasn't working as one script, but ideally it would operate as one):

function createCalendarEvent() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var calendar = CalendarApp.getCalendarById('calendarID');

 

  var startRow = 2;  // First row of data to process - 2 exempts my header row

  var numRows = sheet.getLastRow();   // Number of rows to process

  var numColumns = sheet.getLastColumn();

 

  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);

  var data = dataRange.getValues();

   

  for (var i = 0; i < data.length; ++i) {

    var row = data[i];

    var action = row[0]; //action item

    var department = row[1];  //department

    var contact = row[2]; //department contact 

    var owner = row[3];  //owner

    var start = new Date(row[5]); //start time

    var end = new Date(row[6]); //end time

    var eventID = row[7]; //event marked Done

   

   if (!eventID && start == '') { 

     eventID == null

   }

   if (!eventID && start !== ''){

      var currentCell = sheet.getRange(i+2,8);

      var event = calendar.createEvent(action, start, end, {description: "Department: "+ department + '\r\n' + "Department Contact: "+ contact + '\r\n' + "Owner: " + owner});

          currentCell.setValue(event.getId());      

    } else {

    }

  }

}

function createAllDayCalendarEvent() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var calendar = CalendarApp.getCalendarById('calendarID');

 

  var startRow = 2;  // First row of data to process - 2 exempts my header row

  var numRows = sheet.getLastRow();   // Number of rows to process

  var numColumns = sheet.getLastColumn();

 

  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);

  var data = dataRange.getValues();

   

  for (var i = 0; i < data.length; ++i) {

    var row = data[i];

    var action = row[0]; //action item

    var department = row[1];  //department

    var contact = row[2]; //department contact 

    var owner = row[3];  //owner

    var date = new Date(row[4]);  //due date

    var eventID = row[7]; //event marked Done

    var currentCell = sheet.getRange(i+2,8);

    if (!eventID) {

      var event = calendar.createAllDayEvent(action, date, {description: "Department: "+ department + '\r\n' + "Department Contact: "+ contact + '\r\n' + "Owner: " + owner});

          currentCell.setValue(event.getId());

    }

  }

}

I was running into the issue that when I ran the above script, it would only code the events with data in the start column but wouldn't then loop through and complete the script for the other events. I think that's because, the first condition (in the CreateEvent script) returns an eventID for each row regardless of whether it fits the if condition.

Can anyone support?