0

I'm working on setting up a series of scripts for a google sheet where I want to be able to auto sort based off of when something gets entered into the third column, sorting first by the first column (A date field) and the third column (A time field). I keep getting:

'TypeError: Cannot find function getRange in object Data. (line 19, file "Code").

Any and all assistance would be great! My googling has come up with nothing in reference to what I am doing, and it seems the mixing of other scripts I've found to do the job how I want it to is not great.

    //Assign Variables.

//This variable is for which column the system should be looking at for sorting.  If you edit directly into the first column, then it will sort everything.  Otherwise, it won't sort.
//I may fix this later depending on how the system works.
var SORT_COLUMN_INDEX = 1;

//This variable assigns whether we want to sort by ascending or descending based on a boolean statement.
var ASCENDING = true;

//This will offset where the sorting starts.  We are looking to sort ignoring the top row for the purposes of titles.
var NUMBER_OF_HEADER_ROWS = 1;

var SHEET_NAME = "Data"

//This function is for the autosort.
function autoSort(sheet) {

  //Pulls the range for the sorting (how many rows, etc.)
  var range = sheet.getRange("A1:D999");

  //Offsets range by however many header rows.  If there are none, it ignores this portion.
  if (NUMBER_OF_HEADER_ROWS > 0) {
    range = range.offset(NUMBER_OF_HEADER_ROWS, 0);
}

  //this handles the sorting.
  range.sort([{column: 3, ascending: true}, {column: 1, ascending: true}])
}

//event function that triggers autoSort.
function onEdit(event) {

  //variable to call when a cell gets edited
  var editedCell;

  //assigns a value to edited cell.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  editedCell = sheet.getActiveCell();

  //checks edited cell if the the column is correct.  If so, triggers autosort.
  if (editedCell.getColumn() == SORT_COLUMN_INDEX) {
    autoSort(SHEET_NAME);
  }
}

I do apologize now, this is the first undertaking into Google Scripting I've ever done and it's only a little daunting when I'm passingly familiar with C# just in terms of coding.

0
  • replace your script with:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Data");
var range = sheet.getRange("A2:D");
function onEdit(e) { range.sort([{column: 3, ascending: true}, {column: 1, ascending: true}]); }

  • Wouldn't that include header rows? it's one of the things I'm trying to avoid. – user220818 Jun 14 at 14:37
  • well, then just skip the header rows by changing A1:D to A2:D - answer updated – user0 Jun 14 at 14:38
  • And you are saying to replace the entire script with this? Sorry if it comes off like I'm looking a gift horse in the mouth, just trying to understand the differences between my mess and what you've put up.. :) – user220818 Jun 14 at 14:40
  • yeah, exactly. this one does exactly what you request – user0 Jun 14 at 15:27
  • @user220818 webapps.stackexchange.com/help/someone-answers – user0 Jun 15 at 10:11
0

It's very likely that you are getting the error because you are executing the autosort function without passing a Class Sheet object.

If you didn't this yet, please read https://developers.google.com/apps-script/guides/sheets

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.