title: Google Apps Script for Multiple Find and Replace in Google Sheets
body: <p>Very first question on Stack Exchange so hopefully it makes sense.</p>
  
  <p>Some background: I work in a school environment and am assisting Learning Support staff in creating more readable timetables for certain students.</p>
  
  <p>They are copying the timetable data from our website which contains subject codes, teacher names, and room numbers. It is in the exact same format that you see in the image below - I have simply copied it into Google Sheets.</p>
  
  <p><a href="https://i.stack.imgur.com/hJJXt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hJJXt.png" alt="GoogleSheetsImage"></a></p>
  
  <p>I essentially need to perform a bulk find and replace for all these codes, and expand them fully so that a subject code e.g. 01ENG02 becomes 'English' and a teachers code e.g. JBO becomes "Joe Bloggs"</p>
  
  <p>I have a full list of what I need the codes to expand to - it's just how best to implement this.</p>
  
  <p>Here is some Google Scripts code in which I've found on both Stack Exchange and other sites which I'm using:</p>
  
  <pre><code>function runReplaceInSheet(){
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("StudentTimetableEntry");
  
    // Replace Subject Names
    replaceInSheet(sheet, /\d\dART\d\d/g, "Art");
    replaceInSheet(sheet, /\d\dCCL\d\d/g, "Communication &amp; Culture");
    replaceInSheet(sheet, /\d\dDLT\d\d/g, "Digital Technology");
    replaceInSheet(sheet, /\d\dDRA\d\d/g, "Drama");
  
    // Replace Staff Names  
    replaceInSheet(sheet, 'TED', 'Tahlee Edward');
    replaceInSheet(sheet, 'TLL', 'Tyrone LLoyd');
    replaceInSheet(sheet, 'TMA', 'Timothy Mahone');
    replaceInSheet(sheet, 'TQU', 'Tom Quebec');
  }
  
  function replaceInSheet(sheet, to_replace, replace_with) {
    //get the current data range values as an array
    var values = sheet.getDataRange().getValues();
  
    //loop over the rows in the array
    for (var row in values) {
      //use Array.map to execute a replace call on each of the cells in the row.
      var replaced_values = values[row].map(function(original_value) {
        return original_value.toString().replace(to_replace, replace_with);
      });
  
      //replace the original row values with the replaced values
      values[row] = replaced_values;
    }
  
    //write the updated values to the sheet
    sheet.getDataRange().setValues(values);
  }
  </code></pre>
  
  <p>This works perfectly. However, I have over 150 staff names, and roughly the same number of subject codes. The process reaches the maximum time, and I'm sure there's got to be a better way of coding this.</p>
  
  <p>I'll consider alternative methods, just bear in mind it needs to be as straightforward as possible for the staff who will be using it.</p>