我实际上想做以下事情:1. 当有新条目时自动对工作表进行排序(脚本已经实现,脚本如下所示)让我们将其称为主工作表。主工作表具有以下列标题(名字、姓氏、电子邮件地址等)。2. 在工作表 1 中,我从主工作表中引入了“名字”和“姓氏”。此工作表还从其他工作表中获取与每个名字相对应的数据。对主工作表进行排序时,工作表 1 也会进行排序,但只有名字,与该名字相关的所有其他相应数据现在都不正确对应。因此,这里的挑战是,当更新主文件时,其他文件应该以相同的方式更新,即插入整行。这样可以维护所有其他文件中所有数据的完整性。
这可能吗?过去两周我一直在网上寻找解决方案。任何人都可以帮忙,那就太好了。
////////////////////////////////
// [START modifiable parameters]
// sheetsToSort is a regular expression -- see these sites for more info:
// http://en.wikipedia.org/wiki/Regular_expression
// https://github.com/google/re2/wiki/Syntax
// https://regex101.com/
// https://regexr.com/
var sheetsToSort = /ContactsMaster/i; // use /./i to sort all sheets
var sortByColumn = 1; // column A = 1, B = 2, etc.
var sortAscending = true; // true for ascending, false for descending
// [END modifiable parameters]
////////////////////////////////
/**
* Simple trigger that runs each time the user edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
if (!e) {
throw new Error('Please do not run the script in the script editor window. It runs automatically when you edit the spreadsheet.');
}
autoSortSheet_(e);
}
/**
* Sorts the active sheet by sortByColumn.
*
* @param {Object} e The onEdit() event object.
*/
function autoSortSheet_(e) {
// version 1.2, written by --Hyde 24 April 2020
// - move the selection back to the row that was edited
// - add activate() to follow the edited row -- left out in version 1.1
// - use e.range.getDisplayValue() instead of e.value to support dates
// version 1.1, written by --Hyde 23 April 2020
// - see https://support.google.com/docs/thread/41921405
// version 1.0, written by --Hyde 21 August 2018
// - initial version
// - see https://productforums.google.com/d/topic/docs/j8xXX6_nX8c/discussion
var sheet = e.range.getSheet();
if (!sheet.getName().match(sheetsToSort)
|| e.range.columnStart > sortByColumn
|| sortByColumn > e.range.columnEnd
|| e.range.rowEnd <= sheet.getFrozenRows()) {
return;
}
showMessage_('Sorting sheet...', 2);
var value = e.range.getDisplayValue();
sheet.sort(sortByColumn, sortAscending);
var columnValues = sheet.getRange(sheet.getFrozenRows() + 1, sortByColumn, sheet.getLastRow(), 1).getDisplayValues();
for (var row = 0, numRows = columnValues.length; row < numRows; row++) {
if (columnValues[row][0] === value) {
sheet.getRange(sheet.getFrozenRows() + 1 + row, sortByColumn).activate();
break;
}
}
}
/**
* Shows a message in a pop-up.
*
* @param {String} message The message to show.
* @param {Number} timeoutSeconds Optional. The number of seconds before the message goes away. Defaults to 5.
*/
function showMessage_(message, timeoutSeconds) {
SpreadsheetApp.getActive().toast(message, 'Auto sort sheet', timeoutSeconds || 5);
}