Using Google Slides to link sheets and summarise data for Sites

Problem: I wanted to have live updates from a Google Form, but I didn't want to give everyone access to the raw data. This is how I solved this problem and what I would do differently next time.

I started with an active Google Form response sheet. I didn't want to mess with that sheet because it messes up the formatting especially as I will have to sort the information later. What I decided on doing was creating a second sheet that references the Response Sheet.

To do this I needed to find a way to reference the Response Sheet and merge that with =countif
If the data were on the same sheet, I would write a statement like this:

=countif("BEd (English Language) 2017")

 Unfortunately I had no idea how to reference a different sheet. I tried to find a few searches, but here's what I came up with:

=countif('Form Responses 1'!C:C,"BEd (English Language) 2017")

The title of the sheet goes inside single quotes ' ' Then after that is the range that is range I would like to reference. In this case it is response that happens to be in column C (!C:C)

With this solution, I was able to create a second sheet that would only have the data that I wanted and it looked exactly like the picture above. However, it had one horrible exception. There was a tab in the lower lefthand corner that gave everyone access to the raw data. Not good!

My next solution was to try and find a way to reference a completely different document rather than just a different sheet. To wit, I found this video:

However =importrange is itself a function and it doesn't work with =countif.
Then it hit me. I already had the second sheet that was in the format that I wanted and it already auto updates. So what I did was use =importrange on the auto updating numbers that I wanted. They looked like this:

=IMPORTRANGE("1eojwgEp282U9MeKsgq8kVQDjc7ZuI_c","Sheet2!C2")

The KEY is in double quotes and it is part of the URL address from the source file. After the comma (2nd argument) I have the location of the cell that I want.

Conclusion:
This is where I left it. It's done what I wanted and it works. More documentation can be found here
Now I have one document that I can share and keep people informed about the competition, but I don't give them access to the raw data.

Going forward:
 If I were really thinking, the answer to optimising this is in the name of the function =importRANGE. Because I've made Sheet2 look exactly like I wanted it to look on the website, I could select the whole range and it would only take one line of code instead of all those little bits.

Another way to go about it would be to is to have the summarised data to the right of all the data and that way it wouldn't require the second sheet.

My next big task is to try and find a way to identify the person who has the most books read of a particular genre. 😬