当前位置: 动力学知识库 > 问答 > 编程问答 >

Google spreadsheet script works on create but doesn't any more after going offline or closing document

问题描述:

I’m extremely new to the scripting business, and I’m having trouble making work properly a script I have made. I need to sort my spreadsheet by a column, whenever any of a number of other columns are edited, so I adapted a solution for this I found in a closed topic at the google apps forum, by a user KaseyBrown, which has been of great help (I couldn’t have done this by myself). Here’s my version:

var sheetToSort = "Letras"; // name of sheet to be sorted

var columnToCheck1 = 2; // column to check for edits

var columnToCheck2 = 3; // second column to be checked for edits

var columnToCheck3 = 6;

var columnToCheck4 = 10;

var columnToCheck5 = 11;

var columnToCheck6 = 12;

var columnToCheck7 = 13;

var columnToCheck8 = 14;

var columnToSortBy = 16; // column to sort by; A = 1, B = 2, etc.

var rangeToSort = "A1:P150"; // range of values to be sorted

function onEdit(event) {

var sheet = SpreadsheetApp.getActiveSheet();

var editedCell = sheet.getActiveCell();

if (editedCell.getColumn() == columnToCheck1, columnToCheck2, columnToCheck3, columnToCheck4, columnToCheck5, columnToCheck6, columnToCheck7, columnToCheck8 && sheet.getName() == sheetToSort) {

sortOpenCases();

}

}

function sortOpenCases() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetToSort);

var range = sheet.getRange(rangeToSort);

range.sort( { column : columnToSortBy, ascending: true } );

}

The problem is: when created, the script works great and does exactly what you need it to do, for all the edits you want, UNTIL YOU GO OFFLINE, OR CLOSE THE DOCUMENT. Once you do, the script will no longer work when you return to the sheet, even if you go to the editor and press “run” again. No error message appears. But if I copy it and create it again, it will work again… until I go offline or close the document once more. It happens all the same with Chrome or Mozilla.

Any ideas will be much appreciated; many many thanks in advance for your time;

Juan.

网友答案:

You should try renaming your function 'onEdit' in something else ('sortOnEdit' for example) 'onEdit' is a reserved name that will trigger the function automatically on edit. But this trigger has limited rights. To trigger your function you need to set a trigger form the menu "ressources > Current project's triggers"

An advice: set the notifications to "immediately".

网友答案:

Not Really Meant to be An Answer

Just a little amplifying information regarding the logic in the code.

function onEdit(event) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var editedCell = sheet.getActiveCell();
  if (editedCell.getColumn() == columnToCheck1, columnToCheck2, columnToCheck3, columnToCheck4, columnToCheck5, columnToCheck6, columnToCheck7, columnToCheck8 && sheet.getName() == sheetToSort) {
    sortOpenCases();
  }
}

I didn't understand what was going on with the condition in the if statement so I wrote a little routine to build a truth table for it. I ran it in the script editor and I believe that this part

if (editedCell.getColumn() == columnToCheck1, columnToCheck2, columnToCheck3, columnToCheck4, columnToCheck5, columnToCheck6, columnToCheck7, columnToCheck8

is always true. I then modified the code as shown below:

function do_onEdit(e) 
{
  dispStatus('Do On Edit', 'At the beginning of do_onEdit',400,200);
  var sheetToSort = "Sheet2"; // name of sheet to be sorted
  var columnToCheck1 = 2; // column to check for edits
  var columnToCheck2 = 3; // second column to be checked for edits
  var columnToCheck3 = 6;
  var columnToCheck4 = 10;
  var columnToCheck5 = 11;
  var columnToCheck6 = 12;
  var columnToCheck7 = 13;
  var columnToCheck8 = 14;
  var columnToSortBy = 16; // column to sort by; A = 1, B = 2, etc.
  var rangeToSort = "A1:P150"; // range of values to be sorted

  var sheet = SpreadsheetApp.getActiveSheet();
  var editedCell = sheet.getActiveCell();
  if (editedCell.getColumn() == columnToCheck1, columnToCheck2, columnToCheck3, columnToCheck4, columnToCheck5, columnToCheck6, columnToCheck7, columnToCheck8 && sheet.getName() == sheetToSort) 
  {
    SpreadsheetApp.getUi().alert("I'm busy sorting don't bother me.");
  }
}

and after playing around with making various edits in various columns of the range on each sheet I have to conclude that the only time sortOpenCases() is called is when your editing on the "sheetToSort" which I changed to Sheet2. So effectively the if statement condition is:

function do_onEdit(e) 
{
  dispStatus('Do On Edit', 'At the beginning of do_onEdit',400,200);
  var sheetToSort = "Sheet2"; // name of sheet to be sorted
  var columnToCheck1 = 2; // column to check for edits
  var columnToCheck2 = 3; // second column to be checked for edits
  var columnToCheck3 = 6;
  var columnToCheck4 = 10;
  var columnToCheck5 = 11;
  var columnToCheck6 = 12;
  var columnToCheck7 = 13;
  var columnToCheck8 = 14;
  var columnToSortBy = 16; // column to sort by; A = 1, B = 2, etc.
  var rangeToSort = "A1:P150"; // range of values to be sorted

  var sheet = SpreadsheetApp.getActiveSheet();
  var editedCell = sheet.getActiveCell();
  if (sheet.getName() == sheetToSort) 
  {
    SpreadsheetApp.getUi().alert("I'm busy sorting don't bother me.");
  }
}

I tried a bunch of different edits on various pages and columns with the below code:

function do_onEdit(e) 
{
  dispStatus('Do On Edit', 'At the beginning of do_onEdit',400,200);
  var sheetToSort = "Sheet2"; // name of sheet to be sorted
  var columnToCheck1 = 2; // column to check for edits
  var columnToCheck2 = 3; // second column to be checked for edits
  var columnToCheck3 = 6;
  var columnToCheck4 = 10;
  var columnToCheck5 = 11;
  var columnToCheck6 = 12;
  var columnToCheck7 = 13;
  var columnToCheck8 = 14;
  var columnToSortBy = 16; // column to sort by; A = 1, B = 2, etc.
  var rangeToSort = "A1:P150"; // range of values to be sorted

  var sheet = SpreadsheetApp.getActiveSheet();
  var editedCell = sheet.getActiveCell();
  if (editedCell.getColumn() == columnToCheck1, columnToCheck2, columnToCheck3, columnToCheck4, columnToCheck5, columnToCheck6, columnToCheck7, columnToCheck8 && sheet.getName() == sheetToSort) 
  {
    SpreadsheetApp.getUi().alert("I'm busy sorting don't bother me.");
  }
}

So sorting only occurs when your editing on "sheetToSort" and it occurs on every column on the "sheetToSort". Your welcome to take this last little snippet and test it for yourself as it doesn't actually call sortOpenCases();

@Harold - I learned a lot about how to setup triggers. Thanks for the information.

分享给朋友:
您可能感兴趣的文章:
随机阅读: