In the previous post Automate a Google Slides Yearbook with Autocrat, I demonstrated how to automatically create an individual portraits section of a yearbook like the one below. Now it is time to geek out with the formulas that make Autocrat do its magic!!!!
Off topic, but where did I find those cool avatars?
For the example yearbook, I used avatars as student portraits. I did not want to steal random photos off the internet or violate my student's privacy by using their photos. Instead, my young daughter made cool avatars using Andriodify and Funko, both of which are listed on Ryan O'Donnell's Avatar Listicle. For more ideas from this brilliant educator, please check out his website.
What if formulas scare me?
If formulas are not your cup o' tea, that is okay. You can stop reading now. When you open this example Google Sheets Directory and select Use this template, you also copy all the formulas. One day, this automated process will be made into an easy-to-use add-on. Until then, memorize and practice how to use Autocrat with the example I provided. I will add more templates in the future.
If you are a geek like me, continue reading to learn which formulas Joe used. I have written the directions below and plan to post Joe's tutorial video. Stay tuned.
Now its time to GEEK OUT!
Geeks like myself love formulas, coding, and troubleshooting. We like to look closely at the formulas for inspiration. Yah, we are weird like that.
If you are an expert in this area and notice a mistake in my explanation, please contact me. I don't mind being wrong and I LOVE learning.
Form Responses 1
Columns A, B, C, and E are the Google Forms responses. Columns E and F were added. Joe painted the cells red to indicate where formulas are written.
The reason I have a column for First Name (B) and Last Name (C) is because yearbook portraits are alphabetized by the last name. At the same time, we wanted to minimize the number of columns needed in Sheet 4 (Use for Merge).
Array + Combine = Column D
Arrays work best when extra columns and rows are deleted. Use the Crop add-on to delete blank columns and rows BEFORE adding the array formula.
=arrayformula(if(row(A:A)=1,"combined name",B:B&" "&C:C))
This array says that if there is information in the row, then the formula will combine column B with C. Do you see "combined name" in the formula? That is the name for the column. An array will apply the formula to new rows as they are added.
Array + Substitute = Column F
This formula is amazing! It changes the Google Drive image address to a format that Autocrat uses to insert that image into a Google Slide.
=arrayformula(if(row(C:C)=1,"photo id",substitute(E:E,"open?","uc?export=view&")))
By changing the word "open?" to "uc?export=view&", Autocrat can insert the Drive image into a slide. Previously only public images worked.
Images!!!!
This also means that if you were to add a Column G and use the formula =image("G2"), then the image can be viewed in the Google Sheet!!!!! This is what the array formula looks like:
=arrayformula(if(row(A:A)=1,"photo image",image(F:F)))
Imagine the possibilities!!!
Sorted Responses
A directory is alphabetized by the last name of the student. The one formula for this sheet goes in cell A2.
=sort('Form Responses 1'!A2:F,3,true,2,true)
This formula says to copy and sort columns A2 through F2 in Form Responses 1 sheet. The numbers 3 and 2 identify the order of the sort; Column C (3) is sorted first. Then Column B (2) is sorted.
Transpose
The Transpose function rearranges the information. We need the names and the photo ids in one row. One row = One page in a Google Slide.
=transpose({'Sorted Responses'!D2:D21;'Sorted Responses'!F2:F21})
In the CUE19 example, there are 16 images per page. This means that we need to transpose 16 rows into one row. Notice how Joe skipped lines. This is because the formula transposes16 rows (i.e. rows 2-17). The formula is repeated in 17A and later row 34A. If we had 20 images, then the formula would be added to 2A, 22A, and 42A.
Use for merge
The very last formula filters the information in Transposed sheet into a format that Autocrat can use. The formula for A2 is
=filter(Transposed!A2:AF,Transposed!A2:A<>"")
In the provided example, there are three rows; therefore, there will be three pages in the merged Google Slide.
Can I make a Google Form from the example Directory? Yes, but I kept getting an error about the upload folder. It is easier to make a copy of the Google Form without the upload option.
The Google Form
In the last blog post, I explained how to create a Google Form from the Google Sheets with all the formulas. Here are the steps one more time.
- Make a copy of this Google Sheets.
- Click on Form -> Go to live form.
- Change question 3 from Multiple choice to Upload.
- Optional: only allow images to be uploaded.
- Delete the example image I provided for you.
Everything that I learned about Autocrat comes from Joe Schmidt. Below is his video tutorial on autocrat formulas.
Conclusion
Formulas may be a little scary or mesmerizing. Or both at the same time.
One goal of this project is to create an add-on so that the end user (you) are not required to know the formulas. I want to create a user-friend add-on like Form Publisher that works like Autocrat, but without the formulas.
But the most important goals of the project are to remove expensive yearbook companies from the equation, teach students to become yearbook graphic designers, and dramatically decrease the cost of our student's memory books. Together, you, Joe, and I will make this happen.