Very first question on Stack Exchange so hopefully it makes sense.

Some background: I work in a school environment and am assisting Learning Support staff in creating more readable timetables for certain students.

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.

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 bees 'English' and a teachers code e.g. JBO bees "Joe Bloggs"

I have a full list of what I need the codes to expand to - it's just how best to implement this.

Here is some Google Scripts code in which I've found on both Stack Exchange and other sites which I'm using:

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 & 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);
}

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.

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.

Very first question on Stack Exchange so hopefully it makes sense.

Some background: I work in a school environment and am assisting Learning Support staff in creating more readable timetables for certain students.

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.

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 bees 'English' and a teachers code e.g. JBO bees "Joe Bloggs"

I have a full list of what I need the codes to expand to - it's just how best to implement this.

Here is some Google Scripts code in which I've found on both Stack Exchange and other sites which I'm using:

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 & 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);
}

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.

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.

• javascript
• google-sheets
• google-sheets-api

Share Improve this question Follow edited Oct 14, 2018 at 1:40 tehhowch 9,87244 gold badges2727 silver badges4646 bronze badges asked Feb 10, 2017 at 2:22 StephenStephen 1311 gold badge11 silver badge33 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 4

Each time you call getValues and setValues in your script there is a considerable overhead cost involved and slows your script down. (Google app script timeout ~ 5 minutes?) I modified your above script to make that 1 call for getValues and 1 Call to setValues. The code applies all the replacement to the array in memory before pasting your modified timetable back to the sheet.

function runReplaceInSheet(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("StudentTimetableEntry");
  //  get the current data range values as an array
  //  Fewer calls to access the sheet -> lower overhead 
  var values = sheet.getDataRange().getValues();  

  // Replace Subject Names
  replaceInSheet(values, /\d\dART\d\d/g, "Art");
  replaceInSheet(values, /\d\dCCL\d\d/g, "Communication & Culture");
  replaceInSheet(values, /\d\dDLT\d\d/g, "Digital Technology");
  replaceInSheet(values, /\d\dDRA\d\d/g, "Drama");

  // Replace Staff Names
  replaceInSheet(values, 'TED', 'Tahlee Edward');
  replaceInSheet(values, 'TLL', 'Tyrone LLoyd');
  replaceInSheet(values, 'TMA', 'Timothy Mahone');
  replaceInSheet(values, 'TQU', 'Tom Quebec');

  // Write all updated values to the sheet, at once
  sheet.getDataRange().setValues(values);
}

function replaceInSheet(values, to_replace, replace_with) {
  //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;
  }
}

Give this code a try, if you still have issues with timeouts, my suggestion is setup triggers to help chain functions.