2

I have a script (combined) that runs three functions. First, it splits the form responses into rows (split), then it's supposed to sort the responses alphabetically (sort), and finally delete any rows that contain a blank cell in a specific column. I'm fairly new to using scripts but thought I had this working. Now, when I try to run it or debug, it gives "ReferenceError: "sort" is not defined. (line 3, file "Combined"). I'm sure there's a fairly simple explanation. I have triggers set for this to run on form submit, change, and open.

Anyone willing to review and offer a suggestion? The rest of it seems to be working.

function combined() {
  SPLIT();
  sort();
  deleteRows();


function SPLIT() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

  // get data from sheet 2
  var data = sh1.getDataRange().getValues();

  // create array to hold data
  var aMain = new Array();

  // itterate through data and add to array
  // j=3 is the column it starts to loop with, j<9 tells it where to stop.
  // in the aMain.push line, use data[i][j] for the rows to search and put in the one column.
  // in the last line, change the last number to equal the number of columns in your final sheet.
  // the first number in getrange is the row the data starts on... not sure about the 1.
  for(var i=1, dLen=data.length; i<dLen; i++) {
    for(var j=5; j<9; j++) {
      aMain.push([data[i][0],data[i][1],data[i][2],data[i][3],data[i][4],data[i][j]]);
    }


  // add array of data to first sheet
  sh0.getRange(2, 1, aMain.length, 6).setValues(aMain);
}

function sort() {

  /**  Variables for customization:

  Each column to sort takes two variables: 
      1) the column index (i.e. column A has a colum index of 1
      2) Sort Asecnding -- default is to sort ascending. Set to false to sort descending

  **/

  //Variable for column to sort first

  var sortFirst = 3; //index of column to be sorted by; 1 = column A, 2 = column B, etc.
  var sortFirstAsc = true; //Set to false to sort descending

  //Variables for column to sort second

  var sortSecond = 2;
  var sortSecondAsc = true;

    //Variables for column to sort third

  var sortThird = 6;
  var sortThirdAsc = true;//Number of header rows

  var headerRows = 1; 

  /** End Variables for customization**/

  /** Begin sorting function **/

  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sh0 = ss.getSheets()[0];
  var range = sh0.getRange(headerRows+1, 1, sh0.getMaxRows()-headerRows, sh0.getLastColumn());
  range.sort([{column: sortFirst, ascending: sortFirstAsc}, {column: sortSecond, ascending: sortSecondAsc}, {column: sortThird, ascending: sortThirdAsc},]);


function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  // get data from the first sheet - the first sheet start with 0, we can also name a specific sheet by name but that's a little different setup.
  var sh0 = ss.getSheets()[0];
  var rows = ss.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[5] == 'delete' || row[5] == '') { // This searches all cells in column (change to row[1] for columns B and so on) and deletes row if cell is empty or has value 'delete'.
      sh0.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
}
}
}
}
1
  • are you missing the closing bracket on sort? Commented May 2, 2017 at 13:44

2 Answers 2

1

Don't forget to turn on "API GOOGLE APP SCRIPT" for your account: https://script.google.com/u/1/home/usersettings.

If the "API GOOGLE APP SCRIPT" is off:

  • You can't run the function on the server
  • You always get an error
Sign up to request clarification or add additional context in comments.

1 Comment

Mine was on and I always get this error.
-2

missing closing bracket

function sort() {

  /**  Variables for customization:

  Each column to sort takes two variables: 
      1) the column index (i.e. column A has a colum index of 1
      2) Sort Asecnding -- default is to sort ascending. Set to false to sort descending

  **/

  //Variable for column to sort first

  var sortFirst = 3; //index of column to be sorted by; 1 = column A, 2 = column B, etc.
  var sortFirstAsc = true; //Set to false to sort descending

  //Variables for column to sort second

  var sortSecond = 2;
  var sortSecondAsc = true;

    //Variables for column to sort third

  var sortThird = 6;
  var sortThirdAsc = true;//Number of header rows

  var headerRows = 1; 

  /** End Variables for customization**/

  /** Begin sorting function **/

  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sh0 = ss.getSheets()[0];
  var range = sh0.getRange(headerRows+1, 1, sh0.getMaxRows()-headerRows, sh0.getLastColumn());
  range.sort([{column: sortFirst, ascending: sortFirstAsc}, {column: sortSecond, ascending: sortSecondAsc}, {column: sortThird, ascending: sortThirdAsc},]);
  
  }

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.