Google Apps Script - Round 1: FIGHT!

I needed a solution that would link incremental student submission to formative feedback easily, and though I have long lamented my inability to code with Javascript, I finally decided to commit and do, well, something.

When it comes to coding, building instruments within the Kontakt eco-system has been my only real foray. It suits what I do at the moment and I have ‘learnt the language’ over the last few years through trial and error. The allure of google apps scripts has constantly been at me, and while I had tried in the past, I approached this with a new determination. The options were either, learn to code this little app, or pay an API specialist company $35AUD a month for the duration that I needed the functionality. It funny how money, or having to shell out money, is often a great motivator.

Project

Formative Feedback Solution

Brief

  1. Students submit forms weekly, these forms contain links to resources and ‘evidence’ that demonstrates learning.

  2. These submissions feed into a google sheet.

  3. I provide feedback on the student submissions.

  4. The feedback is then pushed to the students personal googledoc.

It took the best part of 20 hours, but I managed to write this tiny little script, and it works, as described above. I’m absolutely sure that it is no Ada Lovelace masterpiece, but it is something to build upon. I have dubbed it ‘appendTextDoc’ function. Feel free to use as you see fit, if like me you have my very specific problem and need to fix it.

How does it work?

That’s a good question, I’m glad you asked. Assuming the script is pasted into a google apps script document created in a google sheet, Section 1 access the google sheet, and specifies mySheet as the form response sheet. The potential range of columns are stored in myRange. myHeading is a cell containing the word ‘Feedback’, it could be anything you want really.

Section 2 specifies and access another sheet within the spreadsheet called Sheet1, where various data would be stored, in this instance, the number of rows that have been written to on the form response sheet, via a countA formula. This number will change through a school tri/semester and needs to be variable to ensure that the for loop will always look for new entries.

Section 3 loops through all rows, and various cells, and stores the required information in vars, it then checks to see whether the ‘feedback checkbox’ returns true, if it does it pushes the feedback to the appropriate googledoc, and marks the feedback check box as completed.

Improvements

There are many, as this is really just a proof of concept for me. Initially, I will probably restructure the final script to check for the ‘feedback checkbox’ first, then proceed if checked, rather than looping, storing then potentially not using based on that check.

I am by no means a natural coder and I’m sure there are plenty of people out there that could whip this up in minutes, but it was a good experience trawling through the reference guides and forums to nut out a solution myself. Please feel free to use and reuse.

function appendTextDoc() {      
  // Get Spreadsheet and heading - SECTION 1   
  var mySs = SpreadsheetApp.getActiveSpreadsheet(); // Returns this spreadsheet   
  var mySheet = mySs.getSheetByName("Form Responses 2") // Returns sheet via name of the sheet   
  var myRange = mySheet.getRange("A:Z"); // Gets columns that I may need in sheet   
  var myHeading = myRange.getCell(1, 4); // column heading (Feedback)    
  // Get Data Spreadsheet - SECTION 2   
  var myDataSheet = mySs.getSheetByName("Sheet1"); // Returns the data sheet   
  var myDataRange = myDataSheet.getRange("A:B"); // Gets the columns I may need   
  var howManyCells = myDataRange.getCell(27, 2); // variable to store the number of rows to inc through      
  
  // loop through and store all information needed - SECTION 3   
  for (var i = 2; i < howManyCells.getValue()+1; i++) {     
    var myData = myRange.getCell(i, 4); // Gets cell containing feedback, if any     
    var cellValue = myRange.getCell(i, 5); // Checkbox cell, is it 'TRUE' and if so, push the feedback (while loop below)     
    var nameId = myRange.getCell(i, 6); // row that stores the docId        // Open the document and access the body that needs the feedback appended     
    var doc = DocumentApp.openById(nameId.getValue());     
    var body = doc.getBody(); // get body of file id          
    
    // append various text (vars) to doc if cellValue is true     
    if(cellValue.getValue() == true){       
      body.appendParagraph(myHeading.getValue()).setHeading(DocumentApp.ParagraphHeading.HEADING1); // append the heading       
      body.appendParagraph(myData.getValue()).setHeading(DocumentApp.ParagraphHeading.NORMAL); // append the feedback       
      body.appendParagraph(nameId.getValue()).setHeading(DocumentApp.ParagraphHeading.NORMAL); // append the docId       
      body.appendHorizontalRule();       
      cellValue.setValue('Complete');     
    }   
  } 
}