automatically adding column data to google form submissions with a script

I've often come across the issue of wanting to manipulate data that has been submitted with a Google Spreadsheets Form. For example, automatically marking and totaling formative quizzes where students submit their answers in a Google Form.

Unfortunately when a user submits a form, a new row with those data is inserted on the spreadsheet. This means any formulas that you've manually added to the Spreadsheet will be above or below that row.

To solve this issue, I wrote a Script that copies (to that inserted row) the contents of the columns you've added in the first row.


function addFormula() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var startColumn = 8;
  var numberRows = 1;
  var numberColumns = 15;
  var lastRow = sheet.getLastRow();
  var sourceRange = sheet.getRange(startRow, startColumn, numberRows, numberColumns);
  var destinationRange = sheet.getRange(lastRow, startColumn, numberRows, numberColumns);
  sourceRange.copyTo(destinationRange);
};


The meanings of the variables are:
sheet is a shortcut so we don't have to keep typing SpreadsheetApp.getActiveSheet()
startRow is the row number that's the source of your formula that you want to copy
startColumn is the column number where your source formula starts
numberRows should usually be 1, it's the number of rows that you would like to copy each time
numberColumns is the number of columns that contain your source formula
lastRow is a shortcut so we don't have to type sheet.getLastRow() when we want to use it
sourceRange collects together the information to tell copyTo where to get the data
destinationRange collects together the the information to tell copyTo where to put it


If you prefer, this could also be done in a single line without all of the variable declarations:


function addFormula() {SpreadsheetApp.getActiveSheet().getRange(2, 8, 1, 15).copyTo(SpreadsheetApp.getActiveSheet().getRange(SpreadsheetApp.getActiveSheet().getLastRow(), 8, 1, 15))};


To add this script to your spreadsheet of data from a Google Form

1. open the spreadsheet and under the Tools menu choose Script editor...
2. under "Create script for" click Spreadsheet
3. delete everything in the Code.gs pane and replace it with the script from this blog post
4. if necessary, change the numbers for the variables to what they should be for your spreadsheet
5. under the File menu click Save
6. under the Resources menu click Current script's triggers...
7. if you haven't already named your project, do so now in the box that comes up
8. click No triggers set up. Click here to add one now.
9. in the third drop-down list, select On form submit
10. click the Save button

And you're done. The script will run whenever a user submits the form, and it will copy the formulas that you've set up on the first line of submitted data. Leave a comment below if this works for you or if you have any questions.

presenting at Destination Innovation 2012

Today I'm presenting at the Destination Innovation 2012 conference at the Banff Centre on "Makers and the DIY Revolution".

If you want to follow along with the conference, the Twitter hash tag is #Dest_2012.

ISTE12 Conference Closing Keynote

The closing keynote was by Dr. Willie Smits from DeforestACTION together with Liza Heavener and  Chris Gauthier about "global collaborative projects with, for, and by students around the world". Dr. Smits talked about some of the projects that they've been involved in, and some of the work of the foundation.


It wasn't particularly technology-focused, but it was somewhat interesting and applicable to education. It was good to hear about some of the things they are doing, and the tool Earthwatchers that crowdsources students to analyse satellite photos and watch for logging and other related activities to make a difference in the world.


There were many comments on Twitter that this should have been the opening keynote rather than the closing one. Unfortunately, as is often the case, many people had left the conference by the time the keynote started. I suppose the advantage was that there was more seating space and the Wi-Fi worked much better than at other keynote events this week.

A Balance Between Consuming and Sharing

As I've tried to blog about my experiences here at the ISTE conference, I've been forced to think about the balance between gathering or experiencing and making things public for others. It takes time and some effort to digest and to sit and write about what I'm seeing, hearing, and learning.

During this conference I've occasionally found myself writing about one topic while attending a session on something completely different. This has perhaps been detrimental in that I've probably missed some things when I've not been fully present. Task switching is a skill that I'm still working on developing futher.

Over all I think this process been helpful for me, though, to summarize things to help clarify my own thinking.


I'd certainly recommend blogging in general as a way to communicate ideas and share back what you've received. However I think blogging is almost essential when attending a conference that many people are unable to be at, or one as large as this with more sessions than one person could possibly attend.


As the conference winds down, I need to go back over my notes and see what hasn't made it into blog posts or Twitter posts to make sure that I'm sharing as much as I can. I've also realized that this is not just about sharing with others, but also sharing with the future me when I look back at these posts in September.

Meeting People at the ISTE Conference

I've been meeting a lot of great people here, and of course spending time with people that I already know. It's interesting, though, to see the differences in "famous" people here at the ISTE conference. Keynote speakers from other conferences, and even authors who's books are being sold here, are often walking around as attendees. I passed Marc Prensky, from the keynote panel, wearing a conference lanyard and walking with a couple of other attendees. I resisted the urge to ask if I could get a picture with him. I also sat and chatted for a few minutes with Dean Shareski, a past keynote speaker at the ATLE conference, and Steve Dembo, an upcoming keynote speaker at the ATLE conference. On the street I passed Scott Kinney, a former ATLE keynote speaker. I've also seen posts on Twitter by David Warlick, who will be the keynote speaker at our division professional development day, and I've also seen Gary Stager around.

On the other hand, Dr. Mayim Bialik (a mainstream celebrity) was much more guarded. Her time was protected and proscribed, and only certain people were able to get a photo taken with her. Don't get me wrong, she came across as very friendly in the keynote panel and during the session she helped present, but I'm sure if she would have been stuck talking to a thousand people if she hadn't been sheltered.


I have been able to talk to a number of great people here, though. Yesterday I ended up sitting beside Brett Kopf, the very enthusiastic Co-founder of remind101, a service that I often recommend for teachers. I also waited in a line and had a good conversation with John Lindsay, the husband of flat classroom author Julie Lindsay. A few people that I've met and interacted with in person and/or on Twitter are Jenn Wagner, Stacci Barganz, and Amanda Pelsor.


I've also appreciated the opportunity to spend some time with people I know in person, such as Todd Kennedy and Treva Emter from the ATLE, and of course people from my own school district:
Faye McConnell, Director of Education Technology and AISI
Donna Griffin, Innovation Facilitator
Aaron Tuckwood, Technology Consultant
Les Sereda, Media Specialist
Tim Knell, Director of Technology Services
Scott McFadyen, Chief Financial Officer
Ali Nazarali, Support Analyist
Shawna Jenkins, Literacy Consultant
Dianne Molzan, Student Support Consultant
Jenn Cowie, Teacher

Keynote: Dr. Yong Zhao

I'm a fan of Dr. Yong Zhao, he's an engaging speaker and had the audience laughing often. I like his ideas about the problems with standardized testing and how they seem to be negatively correlated to innovation and other useful things.

He talks about how China's education system is admired by many countries, but actually seems to be stifling creativity and entrepreneurial spirit. Instead, he proposes a "future-oriented education starting with each child instead of an authoritative prescription of knowledge and skills." 


In his opinion, things that matter are diversity of talents, creativity, entrepreneurship, passion and confidence.


There were great quote and one liners. I've taken these from Twitter and/or paraphrased them:
"Literacy shouldn't be the national goal; it should be the floor, not the ceiling."
"I would like common core as long as it's not common or the core."
"China has best education system and worst education system combined: High test scores, low creativity/innovation"
"The US education isn't in decline, it has always been bad."
"The problem is, American teachers care more about students than they do about math."
"Our kids are too happy and confident to be good at academics."
"American schools kill creativity less effectively."

Poster and Table Sessions

This afternoon I went to a number of poster sessions and table sessions. I like the concept of poster sessions for topics where you just want some quick information about a topic, or a few minutes to chat with a presenter about a topic. In the time frame of an hour-long session, I was able to have a lot of conversations and get a lot of information about topics I'm interested in, rather than hearing about a single topic.

However I'm not as convinced that the table sessions, for research paper presentations, are as good of an idea. The room was a little small to have seven simultaneous presentations The presenter at my table was trying to play some videos, but even with her little speakers it was very difficult to hear them. On the positive side, though, having about ten people at a table made for a more intimate session and more willingness of participants to ask questions, and I certainly found it valuable to hear about "Gaming in the Classroom: Implicit through Portal 2 Explicit Knowledge".


Some highlights of the poster sessions were:
MCSD Podcast Library
Rubric for Assessing the Quality of Online Environments
Online Roller Coaster Creator for teaching math
Creating Games with eToys