Small Coding Challenge 2

The second coding challenge is up at misterhay.github.io/coding-challenges, along with a possible solution to this challenge and last month's challenge.

Fixing a formula in multiple Google Sheets

Recently we realized that there was a calculation error in a Google Sheet template, and unfortunately we had already made a copy of it for each teacher.

Rather than opening each Sheet and manually making the correction, I wrote a Google Apps Script to automatically make the replacements. Here is the code in case it is helpful for anyone else:

function loopThem() {
  var folderId = 'aaaaaaaaa'; // the folder containing all of the sheets to be corrected

  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    var id = file.getId();
    fixIt(id);
  }
}

function fixIt(id) {
  var rangeToFix = 'P1:P50';  // the range in each sheet that contains the error
  var replaceThis = '$A';
  var replaceWithThis = '$C';

  var ss = SpreadsheetApp.openById(id);
  var range = ss.getRange(rangeToFix);
  var formulas = range.getFormulas();
  for (var i=0; i<formulas.length; i++) {
    var formula = formulas[i][0];
    var newFormula = formula.replaceAll(replaceThis, replaceWithThis);
    formulas[i][0] = newFormula;
  }
  range.setFormulas(formulas);
}