Create event timeline from google sheets data

I've got 1 Google sheet (called DATA) with 3 columns (Start Date, End Date, Event Name) and then another Google sheet (called SCHEDULE) with 1 column of sequential dates (06/01/2018, 06/02/2018, etc).

I want to create a script that will read the Start Date and End Date from the DATA sheet and then creates a box (color-fills the corresponding cells) that spans the date range on the Schedule sheet. I know a fair amount of javascript, but I really don't even know where to start here. What would be the best way to do this?



answered 4 months ago Tony D #1

You’re going to want to familiarize yourself with Google Apps Script for Sheets. This will provide comfort and familiarity with the object model and how to recourse through ranges. I would probably loop through the Schedule sheet, and for each date in the schedule, loop through the data sheet row by row. For each iteration in the inner loop:

  1. Convert all three date strings (1 on schedule and 2 on data) into JS date objects.
  2. Knowing that Date.parse() returns an integer of milliseconds, check it schedule date between data dates.
  3. If yes, color the box?

Does that work?

comments powered by Disqus