We make collaborative Calendar made out of rows for a small scale Website (here build with carrd.co) on the basis of a Google Spreadsheet.

Warnings

Rows of the calander filled with information

The calender I build with it at Carrd

Intro

For this tutorial we will use Carrd.Co as a easy to use and cheap website builder. You could also probalby host this piece somewhere else depending on what you want to use it for.

In this tutorial we will use Javascript to build us HTML Elements with custom CSS. The data for the calender will be supllied by google sheets and events can be added by google forms.

It is a very special use case of the wonderful Idea of using Google Spreadsheets as a backend (for all the data mushing and entering) of an website. That way you can have a collaborative website and use all the functions of Google Sheets including the Google Forms to enter more information without having to open up an entire spreadsheet on your phone. Therefore I would highly recommend watching Chia Amisolas Tutorial on the basics of building such sites Sheets Sites Workshop ✿ 6.17 Live Recording - YouTube and studying her associated Spreadsheet Sites!

Limitation

  1. You are bound to Google and their descension. Even though the spreadsheet is just a JSON File after all witch you could host somewhere else the current workflow uses Google Sheets
  2. Google really does not want to be your internet server. Embedding images uploaded with google drive is too much traffic for your normal google account and refreshments of the calendar are capped at 150 a minute (so not more that 150 should visit your website a minute)
  3. Everything in the spreadsheet is potentially visible to an outsider
  4. You should try to understand what the code does
  5. Spreadsheets are not databases and will not scale for you multi corp but can work wonders for you local community center

Start implementing the current code

  1. Open Carrd.Co if you are paying for the service you can add a "Embed" element. It must be "Code" and styled "Inline" so it will be portrayed in the part where we put the embed on our site
  2. Disable the option "Defer script tags" (its at the very bottom of the side panel) otherwise the script will somehow never be called on. This will also ensure that just with a first visit or refresh of the site will the code actually be called on.
  3. Open up the example spreadsheet VillaSheets - Google Sheets
  4. Create your own spreadsheet it uses the Collum Names I used in my spreadsheet "Veranstaltungstitel","Datum","Zeit" etc.
  5. Change the variables var SPREADSHEET_ID = "PLEASE INFILL"; to what the string of letters and numbers in the URL of your Spreadsheet after https://docs.google.com/spreadsheets/d/.... AND also change the variable TAB_NAME = "Sorted"; to the Name of the Tab it should actually get its information from.
  6. You could read the rest of the post our copy the entire code as I use it from bellow
  7. If you want to edit your code I would advise you to use Notepad++ or Phoenix Code and then just copy over your code

Code explainer

The code is made out of of 3 parts

  1. The general html framing so it works in Carrd.
  2. the style css part which allows it to look how you want it (or in this case how I wanted it)
  3. Then comes the sweet Javascript (JQUERY)

The Javascript part explained

  1. The look up the spreadsheet and loop every row with the code inside the loop(forEach)
$(document).ready(function() {
    var SPREADSHEET_ID = "1P4zhRdXW_l8EpTTw0vIx6W6BCChRtFmzgdbYLEvuLqo";
    var TAB_NAME = "Sorted";

    $.getJSON("https://opensheet.elk.sh/" + SPREADSHEET_ID + "/" + TAB_NAME, function(data) {
        data.forEach(function(entry, index) { </script>
  1. Then comes the part where it decides which HTML container the event comes in. Is it today, is it tomorrow or is it in the past
            var age = entry.AlterInTagen;
            var container = "#current-events";
            var bgColor = "#000000";

            if (age == 0) {
                bgColor = "#FA4B77"; // today's background
            } else if (age < 0) {
                bgColor = "#ED829D"; // future
            } else {
                bgColor = "#ffccdf"; // past
                container = "#past-events";
            }
  1. The eventButton is just a the container for one row where all the cell data will be put in so we define it and add it to the container according to the previous point
let eventButton = $(`<button class="accordion" id=${index} style="background-color:${bgColor}"></button>`).appendTo(container);
  1. The it repeats itself with different cell information per column but it always just adds it into the eventButton of the current row of the loop. Here for example the column Veranstaltungstitel
            if (entry.Veranstaltungstitel) {
                $(`<div class="header-content">
                    <p>${entry.Veranstaltungstitel}</p>
                </div>`).appendTo(eventButton);
  1. Every EventButton probalby has more details so we also have an container that is out of view until the eventbutton is clicked (or touched when you are on mobile) thats the eventDetails. We fill the eventDetails up exactly the same way to point 7 but adding it to the new containter "eventDetails" insted of eventButton
let eventDetails = $(`<div class="panel"></div>`).insertAfter(eventButton);
  1. There is also the "attachAccordionEvents" function which opens on the details when the EventButton is clicked. I will (can) not elaborate on how this works as I do not care it just does <3

the spreadsheet

BUT you also need the spreadsheet. For this part it is really helpful to haved worked with EXCEL or Google Sheets before.
In the code you need to change the variables for which spreadsheet and which tab is referenced. To have extra fun with it you can use a google forms. I saw that in a hostel in croatia and thought huh thats need! You can let not tech savy people input things through google forms with simple questions then these will be randomly added to a new TAB on your spreadsheet. (Optional: but maybe you dont want everything that comes to your spreadsheet automatically showing up on your website. For that you can add another coloumn at the end of the columns from the google forms either asking for a clearance or to hide it. I not wanting to create any more "Kappas" capacity problemens (we dont want to create more overhead for anyone here) for this club added a cell that bans this row if there is anything written in it like no, X, dont put this on the website you get the point. We can use the Filter Option (at the third tab to get rid of it))

So there are five Tabs.

  1. The answers of the google forms in mish mash without any order
  2. added calculations:
    1. How old is the event we wan tto add "AlterInTagen" we use =ARRAYFORMULA(WENN(C2:C>0;HEUTE()-C2:C;HEUTE()-C2:C-100)) - Arrayformulars is soooo helpful because this calculation will be done on any added rows before it. "HEUTE" is german for today so it gets us a negative number if its in the future, and a positive number if its in the past. Based on this value we create a BOOLEAN (=ARRAYFORMULA(J2:J>0)) it is true if its in the past and false if it is in the future. We also get the absolute values so no more negative numbers but I dont know for what we use "ABS"
  3. We sort and filter the fuck out with =SORT(FILTER('Berechnung 2'!A2:L;'Berechnung 2'!I2:I="");10;True;11;True)
    1. The column "I" must be empty as this is the column I use to hide stuff if it isnt wanted. If you wanted someone with admin work you could make this as an it needs to have an X in it or something to have it unlocked.
  4. And in the TAB SORTED we us this formula =SORT(FILTER('Veranstaltung verstecken Filter 3'!A2:M;'Veranstaltung verstecken Filter 3'!A2:A>1);11;True;12;True). We use the binary "Vergangenheit" to sort it first the present because we want it at the top and the after that we want to have it start with today go into the future and when we get to the events that happend we want it to start with the just passend events (yesterday, a week ago). This ensures the list is sorted after the relavancy for the user. But you can sort your list however you want. Arrayformula and SORT will be your best friends on your Journal.

The Google sheets Google Sheets

personal note on the project

Making the webpage for this club was a deeply frustration experience not only because I wanted to learn all of this including javascript. It was mostly for the club itself and how decisions are made there. They decided against 20€ carrd in favor for 200€ squarespace because "it looks nice" without even digging into the tools itself. Anyways I hope this code helps you more than me.

So here is the full code:

<script>
<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<title>Title</title>
<style>
body {
    font-family: monospace;
    font-size: 18px;
}
.events {
    display: flex;
    flex-direction: column;
    width: 100%;
}
#past-events {
    text-decoration: line-through;
    margin-top: 2%;
}
.accordion {
    cursor: pointer;
    display: flex;
    justify-content: space-between;
    width: 100%;
    border: 1px solid #ddd;
    background-color: #f1f1f1;
    font-size: 16px;
    margin-bottom: 5px;
    min-height: 50px;
    transition: background-color 0.3s, transform 0.3s;
}
.title-bar {
    display: flex;
    align-items: center;
    padding: 10px;
    background-color: #170000;
    color: azure;
    font-size: 16px;
    justify-content: space-between;
}
.header-content {
    display: flex;
    justify-content: space-between;
    width: 70%;
}
.date-title {
    display: flex;
    justify-content: flex-end;
    width: 20%;
    padding-right: 2%;
}
.accordion:hover {
    background-color: #ccc;
    filter: hue-rotate(270deg);
}
.panel {
    background-color: white;
    overflow: hidden;
    border: 1px solid #ddd;
    padding: 0 15px;
    font-size: 14px;
    max-height: 0;
    transition: max-height 0.5s ease-out;
}
.panel.open {
    background-color: snow;
    max-height: 500px;
    padding-bottom: 15px;
}
.important-text {
    color: #ff7a49;
    font-weight: bold;
    font-size: large;
}
@media (max-width: 768px) {
    .accordion {
        font-size: 14px;
        padding: 10px;
    }
    .panel {
        padding: 0 10px;
    }
    .title-bar {
        font-size: 14px;
    }
}
</style>
</head>
<body>

<div class="title-bar">
    <div class="header-content">Titel</div>
    <div class="date-title">Datum</div>
    <div class="header-content">Beschreibung</div>
    <div class="date-title">Zeit</div>
</div>

<div id="current-events" class="events"></div>
<div id="past-events" class="events"></div>

<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script>
$(document).ready(function() {
    var SPREADSHEET_ID = "1P4zhRdXW_l8EpTTw0vIx6W6BCChRtFmzgdbYLEvuLqo";
    var TAB_NAME = "Sorted";

    $.getJSON("https://opensheet.elk.sh/" + SPREADSHEET_ID + "/" + TAB_NAME, function(data) {
        data.forEach(function(entry, index) {
            var age = entry.AlterInTagen;
            var container = "#current-events";
            var bgColor = "#000000";

            if (age == 0) {
                bgColor = "#FA4B77"; // today's background
            } else if (age < 0) {
                bgColor = "#ED829D"; // future
            } else {
                bgColor = "#ffccdf"; // past
                container = "#past-events";
            }

            let eventButton = $(`<button class="accordion" id=${index} style="background-color:${bgColor}"></button>`).appendTo(container);

            if (entry.Veranstaltungstitel) {
                $(`<div class="header-content">
                    <p>${entry.Veranstaltungstitel}</p>
                </div>`).appendTo(eventButton);
            }

            var shortDate = String(entry.Datum).slice(0, -5);
            if (entry.Datum) {
                $(`<div class="date-title">
                    <p>${shortDate}</p>
                </div>`).appendTo(eventButton);
            }

            if (entry.KurzeZusammenfassung) {
                $(`<div class="header-content">
                    <p>${entry.KurzeZusammenfassung}</p>
                </div>`).appendTo(eventButton);
            }

            var time = String(entry.Zeit).slice(0, -3);
            if (entry.Zeit) {
                $(`<div class="date-title">
                    <p>${time}</p>
                </div>`).appendTo(eventButton);
            }

            let eventDetails = $(`<div class="panel"></div>`).insertAfter(eventButton);

            if (entry.Datum || entry.Zeit) {
                $(`<p class="important-text">${entry.Datum}<br>${time}</p>`).appendTo(eventDetails);
            }

            if (entry.KurzeZusammenfassung) {
                $(`<p class="important-text">${entry.KurzeZusammenfassung}</p>`).appendTo(eventDetails);
            }

            if (entry.LangeBeschreibung) {
                $(`<p>${entry.LangeBeschreibung}</p>`).appendTo(eventDetails);
            }

            if (entry.EinLink) {
                $(`<a href="${entry.EinLink}">Link: ${entry.EinLink}</a>`).appendTo(eventDetails);
            }
        });

        attachAccordionEvents();
    });

    function attachAccordionEvents() {
        var acc = document.getElementsByClassName("accordion");
        for (var i = 0; i < acc.length; i++) {
            acc[i].addEventListener("click", function () {
                var panel = this.nextElementSibling;

                if (panel.classList.contains("open")) {
                    panel.style.maxHeight = null;
                    panel.classList.remove("open");
                } else {
                    var openPanels = document.querySelectorAll(".panel.open");
                    openPanels.forEach(function(openPanel) {
                        openPanel.style.maxHeight = null;
                        openPanel.classList.remove("open");
                    });
                    panel.style.maxHeight = panel.scrollHeight + "px";
                    panel.classList.add("open");
                }
            });
        }
    }
});
</script>
</body>
</html>

</script>