CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
ujora
ujora

Reputation: 3

Google Apps Script: Create Multiday-Events // Compare Values in Multiple Rows

I've been trying to create a script that would generate a series of all-day events from a list of dates. The list of dates is formatted as follows: 

2020/01/01  promotion offer 1
2020/01/02  promotion offer 1
2020/01/03  promotion offer 2
2020/01/04  promotion offer 2
2020/01/05  promotion offer 2
2020/01/06  promotion offer 3

So what I'm trying to do is do create an event that ranges from 01/01 to 01/02 with the title "promotion offer 1", then another event that ranges from 01/03 to 01/05 and has the title "promotion offer 2" and then another that is just one day and has the title "promotion offer 3".

My approach is to check whether the value in row 1, column 2 differs from the value in row 2, column 2. But I don't know whether I can include that check in a "regular" for loop. So I tried to use two for loops. Which doesn't work :D

This is what I have tried so far: 

var alloffers = spreadsheet.getRange("E133:F141").getValues(); // speichert die Werte in der angegegeben Range in var offerkalender
  
  for (row=0; row<alloffers.length; row++) {
    
    var offer = alloffers[row];
    var date = offer[0];
    var conditions = offer[1];
    
  }
  
  for (row=1; row>alloffers.length; row++) {
  
    var nextoffer = alloffers[row];
    var nextdate = nextoffer[0];
    var nextconditions = nextoffer[1];
    
  }
  
  if (conditions != nextconditions) {
  
    eventCal.createAllDayEvent(conditions, date, nextdate);
    
  }

Anybody able to help? :)

Upvotes: 0

Views: 39

Answers (1)

WilcoSoft
WilcoSoft

Reputation: 146

The comment on your post was heading in the right direction. Easiest way to do this is to check if the new row is equal to the old row, but you also want to keep checking in case there are additional rows. What I've done below is check the values and put them into a new placeholder array for start date, end date, and promotion title. Just change the range in the alloffers variable, and this should work a charm for you.

Note: In testing, I noticed that the calendar app was cutting off the last day of multi-day events. This was due to the handling of time events, so all day events running from 1/1/2020 @ 00:00:00 to 1/3/2020 @ 00:00:00 would translate to being all day events on 1/1/2020 and 1/2/2020 only, so I re-worked the coding to add time onto the end date of multi-day events, thus making it run from 1/1/2020 @ 00:00:00 to 1/3/2020 @ 23:59:59.

  var alloffers = sheet.getRange("Your Range").getValues(); // speichert die Werte in der angegegeben Range in var offerkalender
  // create trimmed offers array
  var trimmedoffers = [];
  // set first array line to the first offer data
  trimmedoffers[0] = [alloffers[0][0],alloffers[0][0],alloffers[0][1]];
  for (var i=1, s=1; i<alloffers.length; i++) {
    // check if offer on this date matches offer on previous date
    if (alloffers[i][1] != alloffers[i-1][1]) {
      // if it does not, set new offer information into trimmed array
      trimmedoffers[s] = [alloffers[i][0],alloffers[i][0],alloffers[i][1]];
      s++;
    } else {
      // if it does, change end date of offer in trimmed array and add on 23:59:59
      trimmedoffers[s-1] = [trimmedoffers[s-1][0],new Date(alloffers[i][0].getTime()+86399000),trimmedoffers[s-1][2]]; 
    }
  }
  // use new array to create events based on total number of offers
  for (var t=0; t<trimmedoffers.length; t++) {
    // check if event is a single day
    if (trimmedoffers[t][0] == trimmedoffers[t][1]) {
      // if it is, create all day event
      eventCal.createAllDayEvent(trimmedoffers[t][2],trimmedoffers[t][0]); 
    } else {
       // if not, create an event from start time to finish time
      eventCal.createEvent(trimmedoffers[t][2],trimmedoffers[t][0],trimmedoffers[t][1]);
    }
  }

Upvotes: 1

Related Questions