In the question you are linking to, the date stamping happens when a new line is edited. If you want the date to be inserted immediately when a new line is added, simply replace EDIT
with INSERT_ROW
:
function onModification(event) {
var sheet = event.source.getActiveSheet();
if (event.changeType == "INSERT_ROW") { // <-- This is true when a new row is inserted
fillEmptyRows(sheet);
}
}
function fillEmptyRows(sheet) {
var range = sheet.getRange(1, 1, sheet.getMaxRows(), 1);
var firstColumnValues = range.getValues();
for (var i = 0; i < firstColumnValues.length; i++) {
if (firstColumnValues[i][0] == "") { // Column 0 is A
firstColumnValues[i][0] = new Date();
}
}
range.setValues(firstColumnValues);
}
The script must be installed as an event handler:
Click Resources -> Triggers -> Add new trigger, select the onModification method and select On change from the right-most dropdown menu.
NOTE: This does not seem to work yet if you have opted in to the new Google Spreadsheets.
First, the error: shs
is an array, since you populate it with ss.getSheets()
, so it doesn't have a method getActiveCell()
. However, the elements of the array has that method.
You are also referencing a variable sheet
, which you haven't defined anywhere.
Currently, your code is more suitable for going through a single sheet, you need to iterate through the shs
array to have it operate on all sheets.
The following function will go through all cells in all sheets:
function onEdit(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var shs = ss.getSheets(), num = shs.length;
var sheetName = []
for (var sheetIdx = 0; sheetIdx < shs.length; sheetIdx++) {
var sheet = shs[sheetIdx];
for(var i = 1; i<num; i++) {
...
}
}
But you wanted to skip the first sheet, so let's start with element 1
in the array (since the array is 0
-based, element 1
is the second sheet):
function onEdit(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var shs = ss.getSheets(), num = shs.length;
var sheetName = []
for (var sheetIdx = 1; sheetIdx < shs.length; sheetIdx++) {
var sheet = shs[sheetIdx];
for(var i = 1; i<num; i++) {
var editedCell = shs.getActiveCell();
var columnToSortBy = 1;
var tableRange = "A2:G99"; // What to sort.
if(editedCell.getColumn() == columnToSortBy){
var range = sheet.getRange(tableRange);
range.sort( { column : columnToSortBy, ascending: false });
}
}
}
}
As for your comment, your code
function onEdit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
for (var n in ss.getSheets()) {
var sheet = ss.getSheets()[n];
var name = sheet.getName();
if (name == 'Dashboard')
return;
var editedCell = ss.getActiveCell();
var columnToSortBy = 1;
var tableRange = "A2:G99";
if (editedCell.getColumn() == columnToSortBy) {
var range = sheet.getRange(tableRange);
range.sort({column: columnToSortBy, ascending: false});
}
}
}
could work, if you replace return
with continue
. You don't want return
, it ends the script. continue
skips to the next element in the for
loop.
Best Answer
There are many approaches to this resolving this question. The key is how to establish whether the first eleven characters of the file name signify a "renamed" file.
The rename format is "yyyy_MM_dd_". The date is separated from the file name by an "underscore". Had you used a different, perhaps unique, separator then you would need only test for that character.
The following script uses the indexOf method (which is zero-based) to test for each of the three underscore characters.
If the combined result is true, then it is a renamed file and the date prefix is stripped from the name to provide the "original" file name for renaming. If the result is false, then the file has not been renamed, and you can continue as normal.