Fast-tracking feedback using Google Apps Script [Release 1] See http://mashe.hawksey.info/2012/01/fast-tracking-feedback-using-google-apps-script-release-1/
// Part of this code up to END OF (c) is: | |
/* | |
Copyright 2011 Martin Hawksey | |
Licensed under the Apache License, Version 2.0 (the "License"); | |
you may not use this file except in compliance with the License. | |
You may obtain a copy of the License at | |
http://www.apache.org/licenses/LICENSE-2.0 | |
Unless required by applicable law or agreed to in writing, software | |
distributed under the License is distributed on an "AS IS" BASIS, | |
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. | |
See the License for the specific language governing permissions and | |
limitations under the License. | |
*/ | |
function onOpen() { | |
var menuEntries = [ {name: "Push Grades to Learners", functionName: "createDocFromSheet"}]; | |
SpreadsheetApp.getActiveSpreadsheet().addMenu("Grading Sheets", menuEntries); | |
} | |
function createDocFromSheet(){ | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var feedsheet = ss.getSheetByName("Feedback_Table"); // get sheet of grades/feedback | |
var infosheet = ss.getSheetByName("Assessment Details"); // get sheet with general assignment info | |
var templateid = infosheet.getRange("B1").getValue(); // get template file id | |
var FOLDER_NAME = infosheet.getRange("B2").getValue(); // folder name of where to put completed diaries | |
var file_prefix = infosheet.getRange("B3").getValue(); // file name prefix | |
var commonData = getColumnsData(infosheet,infosheet.getRange(4,2, infosheet.getMaxRows(), 2)); // get array of common text from assessment details sheet | |
// get spreadsheet values | |
var data = getRowsData(feedsheet); // get all the feedback data | |
for (var i in data){ // setup loop for all the data | |
var row = data[i]; // get just one row of feedback | |
var username=row.username; // get username (email address of student) | |
// loop through common form data and add it to row values | |
for (var a in commonData[0]){ | |
row[a] = commonData[0][a]; | |
} | |
// if feedback hasn't already been generated (checked by existence of document link) generate feedback | |
if (username && (row.documentLink =="" || row.documentLink == undefined)){ | |
// Get document template, copy it as a new temp doc, and save the Doc’s id | |
var copyId = DocsList.getFileById(templateid) | |
.makeCopy(file_prefix+" - "+username) | |
.getId(); | |
var copyDoc = DocumentApp.openById(copyId); | |
// add student as viewer - waiting for google to add addCommenter | |
copyDoc.addViewer(username) ; | |
// move doc to tutors folder | |
var file = DocsList.getFileById(copyId); | |
var folder = DocsList.getFolder(FOLDER_NAME); | |
file.addToFolder(folder); | |
// select the document body | |
var copyBody = copyDoc.getActiveSection(); | |
// find edittable parts of the document | |
var keys = createKeys(copyDoc); | |
// loop through elements replacing text with values from spreadsheet | |
for (var j in keys) { | |
var text = keys[j].text; | |
var replacementText = ""; // set the default replacement text to blank | |
if (row[keys[j].id] != undefined){ // if column value is defined get text | |
replacementText = row[keys[j].id]; | |
} | |
copyBody.replaceText('{%'+keys[j].text+'%}', replacementText); // replace text | |
} | |
copyDoc.saveAndClose(); | |
// create a link to the document in the spreadsheet | |
feedsheet.getRange("A"+parseInt(i)+2).setFormula('=HYPERLINK("'+file.getUrl()+'", "'+copyId+'")'); | |
// you can do other things here like email a link to the docuemnt to the student | |
} | |
} | |
ss.toast("The Grades are published"); | |
} | |
// From James Ferreira's Google Script: Enterprise Application Essentials | |
// function returns an array of unique form keys | |
function createKeys(templateFile){ | |
var templateTxt = getTemplateText(templateFile); | |
var templateVars = templateTxt.match(/\{\%[^\%]+\%\}/g); | |
var templateKeys = []; | |
var oneEach = ""; | |
for (var i in templateVars) { | |
var keyObject = {}; | |
keyObject.text = templateVars[i].replace(/\{\%|\%\}/g, ''); | |
keyObject.id = normalizeHeader(keyObject.text); | |
if (oneEach.match(keyObject.text) == null){ | |
templateKeys.push(keyObject); | |
} | |
oneEach += " " + keyObject.text; | |
} | |
return templateKeys; | |
} | |
// From James Ferreira's Google Script: Enterprise Application Essentials | |
/* | |
* @returns body text from doc | |
*/ | |
function getTemplateText(file){ | |
var output = ""; | |
for (var i = 0; i < file.getNumChildren(); i++) { | |
output += file.getChild(i).asText().getText(); | |
} | |
return output; | |
} | |
// END OF (c) | |
// The rest of this code is currently (c) Google Inc. | |
// setRowsData fills in one row of data per object defined in the objects Array. | |
// For every Column, it checks if data objects define a value for it. | |
// Arguments: | |
// - sheet: the Sheet Object where the data will be written | |
// - objects: an Array of Objects, each of which contains data for a row | |
// - optHeadersRange: a Range of cells where the column headers are defined. This | |
// defaults to the entire first row in sheet. | |
// - optFirstDataRowIndex: index of the first row where data should be written. This | |
// defaults to the row immediately below the headers. | |
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) { | |
var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns()); | |
var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1; | |
var headers = normalizeHeaders(headersRange.getValues()[0]); | |
var data = []; | |
for (var i = 0; i < objects.length; ++i) { | |
var values = [] | |
for (j = 0; j < headers.length; ++j) { | |
var header = headers[j]; | |
values.push(header.length > 0 && objects[i][header] ? objects[i][header] : ""); | |
} | |
data.push(values); | |
} | |
var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(), | |
objects.length, headers.length); | |
destinationRange.setValues(data); | |
} | |
// getRowsData iterates row by row in the input range and returns an array of objects. | |
// Each object contains all the data for a given row, indexed by its normalized column name. | |
// Arguments: | |
// - sheet: the sheet object that contains the data to be processed | |
// - range: the exact range of cells where the data is stored | |
// This argument is optional and it defaults to all the cells except those in the first row | |
// or all the cells below columnHeadersRowIndex (if defined). | |
// - columnHeadersRowIndex: specifies the row number where the column names are stored. | |
// This argument is optional and it defaults to the row immediately above range; | |
// Returns an Array of objects. | |
function getRowsData(sheet, range, columnHeadersRowIndex) { | |
var headersIndex = columnHeadersRowIndex || range ? range.getRowIndex() - 1 : 1; | |
var dataRange = range || | |
sheet.getRange(headersIndex + 1, 1, sheet.getMaxRows() - headersIndex, sheet.getMaxColumns()); | |
var numColumns = dataRange.getEndColumn() - dataRange.getColumn() + 1; | |
var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns); | |
var headers = headersRange.getValues()[0]; | |
return getObjects(dataRange.getValues(), normalizeHeaders(headers)); | |
} | |
// Given a JavaScript 2d Array, this function returns the transposed table. | |
// Arguments: | |
// - data: JavaScript 2d Array | |
// Returns a JavaScript 2d Array | |
// Example: arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]]. | |
function arrayTranspose(data) { | |
if (data.length == 0 || data[0].length == 0) { | |
return null; | |
} | |
var ret = []; | |
for (var i = 0; i < data[0].length; ++i) { | |
ret.push([]); | |
} | |
for (var i = 0; i < data.length; ++i) { | |
for (var j = 0; j < data[i].length; ++j) { | |
ret[j][i] = data[i][j]; | |
} | |
} | |
return ret; | |
} | |
// getColumnsData iterates column by column in the input range and returns an array of objects. | |
// Each object contains all the data for a given column, indexed by its normalized row name. | |
// [Modified by mhawksey] | |
// Arguments: | |
// - sheet: the sheet object that contains the data to be processed | |
// - range: the exact range of cells where the data is stored | |
// This argument is optional and it defaults to all the cells except those in the first column | |
// or all the cells below rowHeadersIndex (if defined). | |
// - columnHeadersIndex: specifies the row number where the column names are stored. | |
// This argument is optional and it defaults to the row immediately above range; | |
// Returns an Array of objects. | |
function getColumnsData(sheet, range, columnHeadersIndex) { | |
var headersIndex = columnHeadersIndex || range ? range.getColumnIndex() - 1 : 1; | |
var dataRange = range || | |
sheet.getRange(1, headersIndex + 1, sheet.getMaxRows(), sheet.getMaxColumns()- headersIndex); | |
var numRows = dataRange.getLastRow() - dataRange.getRow() + 1; | |
var headersRange = sheet.getRange(dataRange.getRow(),headersIndex,numRows,1); | |
var headers = arrayTranspose(headersRange.getValues())[0]; | |
return getObjects(arrayTranspose(dataRange.getValues()), normalizeHeaders(headers)); | |
} | |
// For every row of data in data, generates an object that contains the data. Names of | |
// object fields are defined in keys. | |
// Arguments: | |
// - data: JavaScript 2d array | |
// - keys: Array of Strings that define the property names for the objects to create | |
function getObjects(data, keys) { | |
var objects = []; | |
for (var i = 0; i < data.length; ++i) { | |
var object = {}; | |
var hasData = false; | |
for (var j = 0; j < data[i].length; ++j) { | |
var cellData = data[i][j]; | |
if (isCellEmpty(cellData)) { | |
continue; | |
} | |
object[keys[j]] = cellData; | |
hasData = true; | |
} | |
if (hasData) { | |
objects.push(object); | |
} | |
} | |
return objects; | |
} | |
// Returns an Array of normalized Strings. | |
// Empty Strings are returned for all Strings that could not be successfully normalized. | |
// Arguments: | |
// - headers: Array of Strings to normalize | |
function normalizeHeaders(headers) { | |
var keys = []; | |
for (var i = 0; i < headers.length; ++i) { | |
keys.push(normalizeHeader(headers[i])); | |
} | |
return keys; | |
} | |
// Normalizes a string, by removing all alphanumeric characters and using mixed case | |
// to separate words. The output will always start with a lower case letter. | |
// This function is designed to produce JavaScript object property names. | |
// Arguments: | |
// - header: string to normalize | |
// Examples: | |
// "First Name" -> "firstName" | |
// "Market Cap (millions) -> "marketCapMillions | |
// "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored" | |
function normalizeHeader(header) { | |
var key = ""; | |
var upperCase = false; | |
for (var i = 0; i < header.length; ++i) { | |
var letter = header[i]; | |
if (letter == " " && key.length > 0) { | |
upperCase = true; | |
continue; | |
} | |
//if (!isAlnum(letter)) { // I removed this because result identifiers have '_' in name | |
// continue; | |
//} | |
if (key.length == 0 && isDigit(letter)) { | |
continue; // first character must be a letter | |
} | |
if (upperCase) { | |
upperCase = false; | |
key += letter.toUpperCase(); | |
} else { | |
key += letter.toLowerCase(); | |
} | |
} | |
return key; | |
} | |
// Returns true if the cell where cellData was read from is empty. | |
// Arguments: | |
// - cellData: string | |
function isCellEmpty(cellData) { | |
return typeof(cellData) == "string" && cellData == ""; | |
} | |
// Returns true if the character char is alphabetical, false otherwise. | |
function isAlnum(char) { | |
return char >= 'A' && char <= 'Z' || | |
char >= 'a' && char <= 'z' || | |
isDigit(char); | |
} | |
// Returns true if the character char is a digit, false otherwise. | |
function isDigit(char) { | |
return char >= '0' && char <= '9'; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment