Parallel Practice Logging in Google Forms/Sheets

The goal of this project was to have students logging their practice on translation and a separate non-translation activity.¹ It’s a neat idea and I proposed doing it via Google Forms/sheets because it was a really short timeline.

Here’s a walkthrough of how it ended up with version two. It’s likely we’ll have a more packaged third version that will integrate some more elements.

There’s not too much unique going on in the script but here are a few of the hassles and how I dealt with them.

Connect Weekly Data for Stacked Bar Chart

 weekCell.setFormula("=ISOWEEKNUM(A"+ lastRow+")");

I needed a way to group the data logged per week. ISOWEEKNUM gives you the week number that a particular date occurs in. So this little bit sets the last row of column N to be the ISOWEEKNUM function and referneces the date the form was submitted. This is handy and I’d never run across it before.

Keep it Sorted

function sortByWeek(){
   const sheet = SpreadsheetApp.getActiveSheet();
   const range = sheet.getRange("A2:N");
   range.sort(14);
}

I was trying to figure out how to do the sorting on the QUERY but this seemed easier. This function just keeps the entire sheet ordered by the week number. That way if people enter data later, it doesn’t mess up the graph.

Authorizing IMPORTRANGE via Script

From the beautiful people at Stackoverflow comes this gem. Previously, I could create the sheet and put the IMPORTRANGE function in the cell but I had to go to each sheet and hand authorize the connection. That gets boring fast. This does it automatically which is so very nice.

//from https://stackoverflow.com/a/68498535
function addImportrangePermission(newStudentSheet) {
  // donor or source spreadsheet id
  const donorId = SpreadsheetApp.getActiveSpreadsheet().getId();

  // adding permission by fetching this url
  const url = `https://docs.google.com/spreadsheets/d/${newStudentSheet}/externaldata/addimportrangepermissions?donorDocId=${donorId}`;

  const token = ScriptApp.getOAuthToken();

  const params = {
    method: 'post',
    headers: {
      Authorization: 'Bearer ' + token,
    },
    muteHttpExceptions: true
  };
  
  UrlFetchApp.fetch(url, params);
}

Whole Script

//runs on trigger form submit
function newSubmission(){
  let email = newEntryEmail();
  checkExistingTab(email);//make the internal tab sheets w sub function to make separate view sheet for student
}

//add week count runs on trigger of form submit
function setWeekCount(){
   const sheet = SpreadsheetApp.getActiveSheet();
   const rows = sheet.getDataRange();
   const lastRow = rows.getLastRow();
   const weekCell = sheet.getRange('N'+ lastRow);
   weekCell.setFormula("=ISOWEEKNUM(A"+ lastRow+")");
}


//make sure it stays sorted by week even if things are entered late which was easier than doing a sort on the import at the student sheet level
function sortByWeek(){
   const sheet = SpreadsheetApp.getActiveSheet();
   const range = sheet.getRange("A2:N");
   range.sort(14);
}

//get email
function newEntryEmail(){
   const sheet = SpreadsheetApp.getActiveSheet();
   const rows = sheet.getDataRange();
   const lastRow = rows.getLastRow();
   return sheet.getRange("B"+lastRow).getValue();
}

//check for individual tab for student email
function checkExistingTab(email){
  const workbook = SpreadsheetApp.getActiveSpreadsheet();
  if(workbook.getSheetByName(email)){
    Logger.log('hooray, you exist')
  } else {
    let totalSheets = workbook.getSheets();
    Logger.log(totalSheets.length)
    let newSheet = workbook.insertSheet(totalSheets.length);
    newSheet.setName(email);
    let cell = newSheet.getRange('A1');
    cell.setFormula("=QUERY('Form Responses 1'!A:N, \"Select * WHERE (B ='"+email+"')\")");
     cloneStudentSheet(email);
  }
}

//clone student sheet from template . . . keep in mind you have to manually approve data integration (importrange) with main sheet
function cloneStudentSheet(email){
    const studentTemplate = DriveApp.getFileById('YOURFILEID');
    const destFolder = DriveApp.getFolderById('YOURFOLDERID'); 
    const summarySheet = 'THESUMMARYSHEETURL';
    const newStudentSheet = studentTemplate.makeCopy(email+' reflective practice log', destFolder).getId(); 
    Logger.log(newStudentSheet);
    SpreadsheetApp.openById(newStudentSheet).getRange('A1').setFormula('=IMPORTRANGE("'+summarySheet+'","'+email+'!A:N")');
    addImportrangePermission(newStudentSheet);
}

//from https://stackoverflow.com/a/68498535
function addImportrangePermission(newStudentSheet) {
  // id of the spreadsheet to add permission to import

  // donor or source spreadsheet id, you should get it somewhere
  const donorId = SpreadsheetApp.getActiveSpreadsheet().getId();

  // adding permission by fetching this url
  const url = `https://docs.google.com/spreadsheets/d/${newStudentSheet}/externaldata/addimportrangepermissions?donorDocId=${donorId}`;

  const token = ScriptApp.getOAuthToken();

  const params = {
    method: 'post',
    headers: {
      Authorization: 'Bearer ' + token,
    },
    muteHttpExceptions: true
  };
  
  UrlFetchApp.fetch(url, params);
}