用于在 Google 表格中进行多次查找和替换的 Google Apps 脚本 [英] Google Apps Script for Multiple Find and Replace in Google Sheets

问题描述

关于 Stack Exchange 的第一个问题，所以希望它是有道理的.

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.

他们正在从我们的网站复制包含学科代码、教师姓名和房间号的时间表数据.它与您在下图中看到的格式完全相同 - 我只是将其复制到 Google 表格中.

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.

我基本上需要对所有这些代码执行批量查找和替换，并完全扩展它们以便主题代码例如01ENG02 变为英语"和教师代码，例如JBO 成为Joe Bloggs"

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"

我有一份完整的代码清单，列出了我需要将代码扩展到的内容——这就是实现这一点的最佳方式.

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

这是我在 Stack Exchange 和我正在使用的其他网站上找到的一些 Google Scripts 代码:

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, /ddARTdd/g, "Art");
  replaceInSheet(sheet, /ddCCLdd/g, "Communication & Culture");
  replaceInSheet(sheet, /ddDLTdd/g, "Digital Technology");
  replaceInSheet(sheet, /ddDRAdd/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);
}

这完美地工作.但是，我有 150 多个员工姓名，以及大致相同数量的主题代码.这个过程达到了最长时间，我相信一定有更好的编码方式.

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.

推荐答案

每次在脚本中调用 getValues 和 setValues 都会涉及相当大的开销成本并减慢你的脚本下来.(Google 应用脚本超时 ~ 5 分钟?)我将您的上述脚本修改为对 getValues 进行 1 次调用，对 setValues 进行 1 次调用.在将修改后的时间表粘贴回工作表之前，代码将所有替换应用于内存中的数组.

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, /ddARTdd/g, "Art");
  replaceInSheet(values, /ddCCLdd/g, "Communication & Culture");
  replaceInSheet(values, /ddDLTdd/g, "Digital Technology");
  replaceInSheet(values, /ddDRAdd/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.

这篇关于用于在 Google 表格中进行多次查找和替换的 Google Apps 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！