Jun 24 2015

Google Script – Edit Form Submission update

I just finished an update of an earlier Google Script that I wrote.  The earlier script did two things: First, it added the link to the re-edit URL on the spreadsheet itself so that whoever is managing the sheet can have a record of it.  Second, it automatically emailed the link back to the person submitting the form. The updated version of this script, shown below, adds a bit of functionality. In addition to sending the person submitting the form a link, it also sends them the name and message that they submitted. This allows the person submitting to make sure that everything is fine in their submission. I’ve pasted the code below so you can copy directly from this post.  To use the script, set up a Form and a Response sheet using Google Drive.  Then go to the Response sheet and click Tools > Script Editor. Create a new Spreadsheet Script and delete all of the code that appears.  Paste the code below into that window.  Finally, set up an automatic trigger as described in the comments at the top of the code.  If you have any questions, send me an email and I’ll try to help! 
/* Script updated on 2 August 2015 by Paul Swanson (@teachertechpaul)


TO PEOPLE USING THIS SCRIPT:
This is an updated version of an earlier script designed to record 
the re-edit URL of a form submission and also to email that link back 
to the person who filled out the form. In this update, the email message 
also includes a name field and the comment submitted by the form user.
 
 To use this script, you
 need to do a couple of things:
 
 1 - Edit the 'COLUMN_NUMBER_OF_EMAIL', COLUMN_NUMBER_OF_NAME, 
and COLUMN_NUMBER_OF_MESSAGE variables to match the columns on your 
response sheet that contains the values of those submitting responses
 
 2 - Edit the emailSubject and emailBody variables to match your needs
 
 3 - Click Resources, Current Project Triggers. Click the link:
 'No triggers set up. Click here to add one now.'
 
 Set your trigger with the following properties:
 - Run: 'editFormSubmission'
 - Events: 'From spreadsheet' and 'On form submit'
 
 That's it! If you like this script, email me at paul@teacherpaul.org

*/
COLUMN_NUMBER_OF_TIMESTAMP = 1;
COLUMN_NUMBER_OF_EMAIL = 2;
COLUMN_NUMBER_OF_NAME = 3;
COLUMN_NUMBER_OF_MESSAGE = 4;

function editFormSubmission(e) {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName('Form Responses 1');
 
 /* First we get the Edit URL of the form submission where timestamps match */
 var formResponses = FormApp.openByUrl(ss.getFormUrl()).getResponses();
 var lastResponse = formResponses[compareTimeStamps(e, formResponses)];
 var formEditUrl = lastResponse.getEditResponseUrl();
 
 /* Next we set the formEditUrl as a link on the Spreadsheet itself */
 var responseRow = e.range.getRow();
 var editUrlCol = getEditUrlCol(sheet, "Form Edit URL");
 var formEditCell = '=HYPERLINK("' + formEditUrl + '","Edit Link")';
 sheet.getRange(responseRow, editUrlCol).setValue(formEditCell);
 
 /* Then we create variables to send in our email */
 var emailAddress = sheet.getRange(responseRow, COLUMN_NUMBER_OF_EMAIL).getValue(); 
 var submittedName = sheet.getRange(responseRow, COLUMN_NUMBER_OF_NAME).getValue();
 var submittedMessage = sheet.getRange(responseRow, COLUMN_NUMBER_OF_MESSAGE).getValue();
 
 var emailSubject = "Link to Re-Edit your Submission";
 var emailBody = "Thank you " + submittedName + "," + "<br><br>
Please confirm the following information. If you would like to edit it, please click " + '<a href="' + formEditUrl + '">this link.</a>' + "<br><br>Student Name: " + submittedName + "<br>Comment: " + submittedMessage + "<br><br>Regards";
 
 /* Finally we log the information and email it to the recipient */
 Logger.log("Send email to " + emailAddress + " with subject: " + emailSubject); 
 Logger.log("Email body: " + emailBody);
 MailApp.sendEmail({
 to: emailAddress,
 subject: emailSubject,
 htmlBody: emailBody
 });
}

/* The following function converts the form submission timestamp into milliseconds, then compares it to the timestamps on the spreadsheet.
This ensures that the correct response is selected for the Edit URL */
function compareTimeStamps(e, formResponses) {
 
 // variable checktime is used to compare the timestamp on the spreadsheet.
 var checkTime = new Date(e.values[COLUMN_NUMBER_OF_TIMESTAMP-1]).getTime();
 
 // variable timeDifference is initialized as the number of millis between the first form timestamp and checktime
 var timeDifference = Math.abs(new Date(formResponses[0].getTimestamp()).getTime() - checkTime);
 var closestResponseNumber = 0;
 
 for (var responseNumber = 0; responseNumber < formResponses.length; responseNumber++) {
 var formResponse = formResponses[responseNumber];
 var newTimeDifference = Math.abs(new Date(formResponse.getTimestamp()).getTime() - checkTime);
 if (newTimeDifference < timeDifference ) {
 timeDifference = newTimeDifference;
 closestResponseNumber = responseNumber;
 Logger.log("Closer time match found: response " + responseNumber + " at " + timeDifference + " millis"); 
 } 
 }
 return closestResponseNumber;
}
 
 /* The following function either finds and returns the column number 
that is headed "Form Edit URL", or it creates a column titled this 
 */
 function getEditUrlCol(sheet, headerStr){
   for (var col=1; col<= sheet.getLastColumn(); col++) {
     if (sheet.getRange(1, col).getValue() == headerStr) {
       return col;
     }
   }
 
   // If this section runs, it means header is not established
   sheet.getRange(1, sheet.getLastColumn() + 1).setValue("Form Edit URL");
   return (sheet.getLastColumn());
 
 }

By • Google Script • 1

Jun 4 2015

Digital Footprint focus

After some feedback on my initial final project proposal for COETAIL Course 5, I’m focusing it on one of the components in the broader ATL picture: Social Media and Digital Footprints. Right now I’m trying to avoid the term Digital Citizenship because I’ve found that nearly everyone means something different when they talk about it. Moreover, they tend to use the term ‘digital citizenship’ in reference to ‘that thing that if only we did correctly would solve all of our tech problems.’ Silver bullets like that don’t exist, and lumping together social media, search skills, media production, and everything else doesn’t help very much.

Instead, I’m focusing on Grade 8 since they already have a unit on Citizenship throughout the ages, part of which focuses on Digital Citizenship (in this context, the term citizenship makes a bit more sense). Grade 8 also makes a lot of sense because it is the age at which students are legally able to begin accessing social media sites (ie. they have turned 13), which means that we need to be sure they understand how to manage their online presence.

By • Blog • 0

May 31 2015

Planning for COETAIL Final Project

Last year I was part of a Technology Curriculum group that met several times throughout the year to discuss our school’s philosophy of technology and design as well as how we sought to articulate our curriculum both vertically and horizontally. The philosophy statement we came up with was:

Humans design, use and interact with technologies in innovative ways to address needs and challenges in their lives and in the world.

Technologies assist and enhance learning in all subject areas. Students also need to learn the skills and processes of design thinking to become capable innovators.

UNIS Hanoi strives to develop young people who are confident in addressing the needs and challenges of individuals and communities through the design process.

Learning about the interaction between design and technologies helps students make discerning decisions and develop solutions that shape a more sustainable future.

During these conversations, it became clear that technology could not be limited to discrete subjects (ie. Tech Class), nor could it solely reside as ‘tech integration’. It doesn’t make sense to teach kids how to create a Powerpoint simply to learn the software; the students need to learn the software to accomplish an actual class goal. However, some subjects (like programming) can not be taught by most teachers within the context of other subjects. To learn how to program, you need to take a class in programming.

This year, our principal was concerned about whether or not our assumptions about student knowledge regarding technology were accurate. Were there students falling through the cracks? How do we know whether they have the technology skills that we assume they have at each grade level?

He asked me to identify a few key areas that we could focus on so we could present them to the department heads and see where they fit within the curriculum.  The core areas of tech skills that I came up with were:

  • basic knowledge of hardware, operating systems, and software
  • digital citizenship
  • information management
  • media production
  • research skills

Afterwards, I began working with our Director of Learning to see how these ideas would fit within the broader curriculum. She immediately connected them with the MYP’s Approaches to Learning, a set of transdisciplinary skills that cover five central areas:

  • Communication
  • Social Skills
  • Self-Management
  • Research Skills
  • Thinking Skills

The MYP does not spell out exactly what these skills look like, because the idea is that each school needs to develop their own vision and implementation. As I looked at the examples they gave, however, it became very clear to me that technology was a strand running through all five areas.

This raised a broader question: Do we look at technology as a subject unto itself and see how it fits within each of the ATL areas? Or do we look at each of the different ATL areas separately and weave technology use into it?

I do not have an answer to these questions, but one of my goals for next year is to work with our Director of Learning principal, and all of the teachers to create a comprehensive picture of when, what, how and why we are teaching different tech skills across the school. Since a project like this depends on the coordinated efforts of a large number of people, I am not sure about how feasible it will be. However, I do know that it needs to happen and that for the sake of our students we need to be able to fully support them and their use of technology at UNIS Hanoi.

By • Blog • 0

< 1 2 3 4 5 >»