Coding Without Coding

Lesson 1: What is JSON?

Understanding JSON

JSON (JavaScript Object Notation) is a lightweight format used to store and exchange data. It is easy to read and widely used in web applications. You can watch this short video to find out more but don't worry too much about it. For the purposes of our course, you don't need to understand it!

Example JSON Structure

[
    {
        "name": "France",
        "answer": "The capital of France is Paris.",
        "flag": "https://flagcdn.com/w320/fr.png"
    },
    {
        "name": "Germany",
        "answer": "The capital of Germany is Berlin.",
        "flag": "https://flagcdn.com/w320/de.png"
    }
]

Lesson 2: Entering the Scripting Arena

Hello, Google Apps Script

We're going to start using Google Apps Script now. At first coding can look intimidating and if you had to write it from scratch, it can be, but when we generate the code through ChatGPT, we can learn what it outputs.

In our spreadsheet we have 2 pieces of data - countries and answers to our question. ChatGPT is going to convert those into the special JSON format so our website can understand the data. We still have to add the flags to the spreadsheet so this won't be our final file so the purpose of this module is to get you into the scripting area and where to put the code. Ready?

In your spreadsheet, you'll see a menu -> Extensions and in there you'll find Apps Script

Once you click the menu item, a new tab will open, which should look quite boring and blank.

All we are going to do is copy/paste some code into the coding canvas to see what happens. Delete all of the existing code and then copy the following script into the canvas:

function saveJSONToDrive() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var data = sheet.getDataRange().getValues();
    
    var result = [];
    for (var i = 1; i < data.length; i++) { // Skip header row
        result.push({
            name: data[i][0],  
            answer: data[i][1]
        });
    }
    
    var jsonString = JSON.stringify(result, null, 2);
    var fileName = "country_data.json";
    var folder = DriveApp.getRootFolder();
    var files = folder.getFilesByName(fileName);
    
    while (files.hasNext()) {
        var file = files.next();
        file.setTrashed(true);
    }
    
    var newFile = folder.createFile(fileName, jsonString, MimeType.PLAIN_TEXT);
    Logger.log("File saved: " + newFile.getUrl());
}

Running the Script

To run this script, you firstly have to save your work. You'll see a little Save icon like this one.

Click it and then you are ready to run your first Google Script. Don't get too excited! Click run.

The first time you do this, Google will ask you to review permissions.

Simple click Review Permission and....

😱

Don't panic, you'll be greeted with this scary looking screen from Google. I don't know why. You need to do the following: 1. Click the link that says Advanced, 2. Click the link that says Go to Untitled project (unsafe).

It's mad how long it took me to trust I wasn't being scammed because Google makes it so scary. Anyway, you'll get one more screen.

This one is simple enough. Make sure all the boxes are checked and click the blue button and your script will run!

It only takes a couple of seconds and what you'll see at the bottom window is something like the following:

Basically the code has made a file in your Google Drive and it has outputted its address. The address starts with https:// and a load of gobbledegook so you should highlight it all and open a new tab in your browser (Ctrl + T) (or Cmd + T on a Mac) and paste it in the address bar.

Congratulations! You've created your first JSON file!

Lesson 3: Adding Flags (In the Next Module)

In the next module, we will generate the flags to make our site look nice and we'll add them to our JSON data.