用于Google表格中多个查找和替换的Google Apps脚本 [英] Google Apps Script for Multiple Find and Replace in Google Sheets
问题描述
一些背景：我在学校环境中工作，并协助学习支持人员创建更具可读性的时间表对于某些学生来说。
他们正在复制我们网站上的时间表数据，其中包含学科代码，教师姓名和房间号码。它与您在下面的图片中看到的格式完全相同 - 我只是将其复制到Google表格中。
我基本上需要执行批量查找并替换所有这些代码，并将其完全展开这样一个主题代码，例如01ENG02成为英语，教师代码例如JBO成为Joe Bloggs
我有我需要的代码扩展到的完整列表 - 这只是如何最好地实现这一点。
以下是我在Stack Exchange和我正在使用的其他网站上找到的一些Google脚本代码：
function runReplaceInSheet（）{
var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（StudentTimetableEntry）;
//替换主题名称
replaceInSheet（sheet，/ \d\dART\d\d / g，Art）;
replaceInSheet（sheet，/ \\\\\\\\\\\\\\\\
replaceInSheet（sheet，/ \d\dDLT\d\d / g，Digital Technology）;
replaceInSheet（sheet，/ \d\dDRA\d\d / g，Drama）;
//替换员工姓名
replaceInSheet（表，'TED'，'Tahlee Edward'）;
replaceInSheet（sheet，'TLL'，'Tyrone LLoyd'）;
replaceInSheet（sheet，'TMA'，'Timothy Mahone'）;
replaceInSheet（表，'TQU'，'汤姆魁北克'）;
$ b函数replaceInSheet（sheet，to_replace，replace_with）{
//将当前数据范围值作为数组获取
var values = sheet.getDataRange（） .getValues（）;
//遍历数组中的行
for（var row in values）{
//使用Array.map对每个数组执行替换调用行中的单元格。
var replaced_values = values [row] .map（function（original_value）{
return original_value.toString（）。replace（to_replace，replace_with）;
}）;
//用替换后的值替换原始行值
values [row] = replaced_values;
}
//将更新后的值写入图纸
sheet.getDataRange（）。setValues（values）;
}
这很好。但是，我拥有超过150名员工姓名和大致相同数量的学科代码。这个过程达到了最大限度的时间，我确信有一个更好的编码方式。
我会考虑其他方法，请记住它对于将要使用它的工作人员而言，需要尽可能简单。
每次在您的电话中调用getValues和setValues脚本会涉及相当大的开销成本，并会降低脚本的速度。 （ Google应用程序脚本超时〜5分钟？）我将上面的脚本修改为使1调用getValues和1调用setValues。该代码在粘贴修改后的时间表之前应用所有替换。
function runReplaceInSheet（）{
var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（StudentTimetableEntry）;
//将当前数据范围值作为数组获取
//较少调用访问表单，降低开销
var values = sheet.getDataRange（）。getValues（）;
//替换主题名称
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）;
//替换员工姓名
replaceInSheet（值，'TED'，'Tahlee Edward'）;
replaceInSheet（值，'TLL'，'Tyrone LLoyd'）;
replaceInSheet（值，'TMA'，'Timothy Mahone'）;
replaceInSheet（值，'TQU'，'汤姆魁北克'）;
//将更新后的值写入表单，再次调用less;减少开销
sheet.getDataRange（）。setValues（values）;
函数replaceInSheet（values，to_replace，replace_with）{
//遍历数组中的行
for（ var row in values）{
//使用Array.map在行中的每个单元格上执行替换调用。
var replaced_values = values [row] .map（function（original_value）{
return original_value.toString（）。replace（to_replace，replace_with）;
}）;
//用替换后的值替换原始行值
values [row] = replaced_values;
}
}
试试这段代码，如果你仍然遇到超时问题，我的建议是设置触发器来帮助链功能。
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 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.
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.
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 before pasting your modified timetable back.
function runReplaceInSheet(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("StudentTimetableEntry");
// get the current data range values as an array
// Lesser 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 the updated values to the sheet, again less call;less overhead
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屋！
