SlidesYearbook is a Google Innovator Project that was launched in December 2016 with the #Mex16 cohort. Below is my application video with English subtitles. Please note that the current innovator application does not want the applicant to suggest solutions. Finding solutions is what you should do at the Academy.
Yearbooks are more than photographs. They remind us that we survived. Memory books stay with our families for generations. The problem is that many families cannot afford to purchase one. Google Yearbook includes a Slides Add-on and federally mandated privacy settings in Google Photos. The students will work collaboratively, use facial recognition, and send the final product to a printing company. For the past two years, my junior high school has designed the yearbook with Google Apps with 100 pages and full-color for only $8.16. Yearbooks remind us of teachers who believed in us, of obstacles we have overcome, and of hope for the future.
Why did I revisit my video application? One reason was to apologize for not spending as much time on the Spanish version of the website. The other was to proclaim that I have coded the add-on - at least the beginnings of it!!!!!!
Please understand that Google Slides Add-ons did not exist in December 2016. In fact, Google Slides Add-ons were not launched until November 2017. I dreamt of a solution that I had no idea could happen. The lesson is DREAM BIG!!
Innovation occurs when problems try to stop us. For years, my students have inserted photos one by one with no issues. For years, I have used the photographer provided software (that I loathed) to create the individual portraits' section. But this year, Jon Corippo of CUE asked four of my editors to create the first official Spring CUE Yearbook. Uploading photos one-by-one and using out-dated software was not an option.
The innovative solution for Spring CUE was to gather portraits with names (or photos with captions) in a Google Form. The responses went to a Google Sheets and, with the help from Joe Schmidt, formulas were added to prepare the photos for the Autocrat add-on. Adding formulas took a great deal of time and left a lot of room for error.
Alice Keeler, who is teaching me to code in her Go Slow Online Worktops, wrote a blog post in 2017 called Coding Will Save You Hours Of Your Life. The idea that coding will save me and others time is why I am learning how to code a Google apps script.
Today's post will highlight what I have created so far and my ideas for improvement. The post is rather technical but I need the space flesh out the successes and room for improvement. Please contact me via email or Twitter if you have ideas or can help. Remember, the goal of this project is to make yearbooks affordable.
Menu
Room for Improvement
While the drop-down menu is nice, I would like to create a sidebar with step-by-step directions. This will require html skills in addition to coding.
Step 1: Gather Portraits
Gather Portraits
When the user selects Gather Portraits from the drop-down menu, a Google Form is created that is linked to the current sheet. Names are in two separate questions because the last names will need to be alphabetized.
The user needs to update the Role (Section) to reflect their school and changed Upload portrait from the default text question to Upload file.
I could not find an option to automatically create an Upload question from code. This may be because a folder is created when the option is selected.
Room for Improvement
I am debating the require email question. I have it there because an individual, such as a #Lon19 Innovator, may want to resubmit their photo.
Please note that a final add-on will include a solution that pulls photos from a Google Drive folder. Hand uploading photos for 700 students is not practical.
I noticed that the sheet is created to the left of the original sheet. I need to add code to move tab. The user may be confused when nothing appears to have changed.
Step 2: Prepare Sheets 16
Form Responses 1
- Delete blank columns
- Delete blanks rows
- Column E Insert column to right
- Column F =arrayformula(if(row(C:C)=1,"combined name",D:D&" "&E:E))
- Color F1 red
- Column I =arrayformula(if(row(C:C)=1,"photo",substitute(G:G,"open?","uc?export=view&")))
- Color I1 red
- Color tab red
Form Responses 1
Once the photos have been uploaded to the Google Form or pulled from a Google Drive folder (which has not been coded yet), the data goes to a Google Sheet called Form Responses 1. The formulas that need to be added are arrays, which require that all blank columns and rows be deleted. I added the red color to the tab to let the user know that this tab is no longer needed.
The most important formula is listed in #6. To push images to Google Slides, the address must be changed from open? to uc?export=view&.
Room for Improvement
I may make a separate step called Sort for this page. I will write more about this in the next section.
Sorted Responses
- Insert sheet
- Rename Sorted Responses
- A1 Copy/paste Row 1 of Form Responses
- Freeze Row 1
- A2 =sort(filter('Form Responses 1'!A2:I,'Form Responses 1'!C2:C<>""),3,true,5,true, 4, true)
- Color A2 red
- Hide tab
Sorted Responses
Individual portraits are alphabetized so that individuals can be easily located. Any changes made to this sheet results in missing data. This is why I hid the tab.
Room for Improvement
I wonder if a separate tab is necessary? My friend Alicia is the teacher at my children's school. She grouped her school's individual portraits by teacher AND noticed that some students had not taken their Fall yearbook portraits with the professional photography company. She took out her camera and fixed the problem. Alicia's desire to include all students to the yearbook is what we should strive.
Including all students in the yearbook means adding student portraits at a later date. What if Sort was an option for the Form Responses tab? Then, after the students have been added, the user can re-sort the names by section and last name.
What if I could highlight every 16/32 rows (starting on Row 17) so that the user can see where a new page begins? I currently have no solution to separate sections. The user can add rows to fix this.
Transpose
- Insert sheet
- Rename Transpose
- Insert Columns up to AF
- Color A1 yellow
- Color Q1 light green
- A1:P1 name 1 - name 16
- Q1:AF1 image 1 - image 16
- Start at A2 =transpose({'Sorted Responses'!F2:F17;'Sorted Responses'!H2:H17})
- Color A2 red
- Every 16 to 1010 copy/paste formula
- Hide
Transpose
While Transpose only has one formula, it is the most technical sheet. This section layouts the names and images as they will appear on single pages. I had to copy the formula every 16 rows starting at row 2 and ending at row 994.
Room for Improvement
Some schools have more than 993 students. I need to add rows for up to 2,000 students. I am also debating changing 16 portraits for a single page to 32 portraits for two pages. Why this simple change? Even and odd pages have different margins. If I lay out the merge template as two pages, then the user does not have to make those edits on her own. This requires that I change the code to add more columns and paste the formula every 32 lines.
Use for merge
- Insert sheet
- Rename Use for merge
- Insert Columns up to AF
- A1 copy/paste Row 1 of Transposed
- Freeze Row 1
- A2 =filter(Transpose!A2:AV,Transpose!A2:A<>"")
- Color cell red
- Color tab green
Use for merge
This tab is required to run the Autocrat add-on. Each row equals a page in the yearbook (or a template with two pages of merge tags). Any changes to this page result in all names disappearing.
I especially like how I moved the tab to the left and colored it green. This saves a step when using Autocrat.
Step 3: Change Share Settings
Technically, I have not coded this step yet. All I did was create an alert telling the user to manually change the share of the portraits folder to ANYONE WITH A LINK CAN VIEW.
I know that I can code this but many of the examples I have seen in other add-ons are confusing. The folders that appear are recent folders, not a replica of My Drive, which makes finding the needed folder harder.
Step 4: Portrait Template
Portrait Template
When the user selects Portrait Template, a ready-made template complete with merge tags is emailed to the user.
Note
I learned to code in 1995 thanks to college English Professor Emily Thiroux. She won't remember me from thousands of students in the past 24 years, but she gave a choice assignment that included the option to make a website. I tried Frontpage editor and hated it. Instead, I learned HTML from scratch.
This one choice assignment my second year of college launched my webpage design career, started a two-year career in IT, and resulted in my first teaching job where I instructed Reading, Science, and web page development. I have been at that school for 19 years now. That one class indirectly launched this Google Innovator Project.
Room for Improvement
I need to use HTML to make the email look more professional and make the email functional. I want the email to include options for different template designs the user can choose.
Step 5: Launch Autocrat
Autocrat
The script I have written prepares the Google Sheets for Autocrat, the true powerhouse.
Dream for Improvement
New Visions Cloud Lab created Autocrat. Wouldn't it be wonderful if I could either 1) launch Autocrat from the SlidesYearbook add-on or 2) if New Visions became a SlidesYearbook partner so that we could combine the app?
To see the beta version of the SlidesYearbook Sheets Add-on in action, watch the following 7-minute video.
The SlidesYearbook Portraits Add-on is not done. There is still so much more work to do, but enough has been completed to share with you. When I submitted my application to the #Mex16 Google Innovator Academy, I dreamt of an add-on that would make creating a yearbook in Google Slides easier. Three years later, I have created a free website with directions, an online college course, and started the add-on I dreamt of designing.