"The starting column of the range is too small" error

user_3277817 0
Viewed 14693

Here's an example of my script that is working:

    function onFormSubmit(e) {
var date = e.values[0];
var user = e.values[1];
var busUnit = e.values[2];
var body = "Request for Add-On form submission" +"\n\n"+"Date:\t\t\t\t"+date +"\n"+ "Employee Email:\t\t"+user +"\n"+"Business Unit:\t\t"+busUnit +"\n\n" +"Form Responses:\t\t" + "https://docs.google.com/spreadsheets/d/ID/";

    if (busUnit=="Acme") 
  {
    MailApp.sendEmail("admin@acme.com", "Request for Add-On Submission",body);
    }
    
    if (busUnit=="fakename")
   {
     MailApp.sendEmail("admin@fakename.com", "Request for Add-On Submission",body);
     }
   
   if (busUnit=="fakename2")
   {
     MailApp.sendEmail("admin@fakename.com", "Request for Add-On Submission",body);
     }

}

function getColIndexByName(colName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var numColumns = sheet.getLastColumn();
  var row = sheet.getRange(1, 1, 1, numColumns).getValues();
  for (i in row[0]) {
    var name = row[0][i];
    if (name == colName) {
      return parseInt(i) + 1;
    }
  }
  return -1;
}

function emailStatusUpdates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
  var Email = "alladmins@fakename.com";
  var subject = "Add-On Request Update";
  var body = "The Fake Team has updated the status of the latest request.\n\nStatus: " + sheet.getRange(row, getColIndexByName("approval status")).getValue();
  body += "\n\nRequest: " + sheet.getRange(row, getColIndexByName("What are you requesting to have approved?")).getValue();
  body += "\n\nDescription: " + sheet.getRange(row, getColIndexByName("Provide a brief description")).getValue();
 //body += "\n\nNotes: " + sheet.getRange(row, getColIndexByName("Notes")).getValue();
 //body += "\n\nURL: "+ "https://docs.google.com/spreadsheets/d/ID/";

  MailApp.sendEmail(Email, subject, body, {name:"The Fake Team"});
}

function onOpen() {
  var subMenus = [{name:"Send Approval Status", functionName: "emailStatusUpdates"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Send Status Update", subMenus);
}

I want to enable the last 2 body += rows. When I do that, I get this error:

Exception: The starting column of the range is too small.at emailStatusUpdates(Code:45:99)

I tried changing this line to var row = sheet.getRange(1, 1, 1, 4, numColumns).getValues();, but then I get this error:

Exception: The parameters (number,number,number,number) don't match the method signature for SpreadsheetApp.Sheet.getRange. at getColIndexByName(Code:30:19) at emailStatusUpdates(Code:45:113)

google-apps-script google-sheets
2 Answers

Try this:

function getColIndexByName(colName) {
  var sh=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var hA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
  var idx={};
  hA.forEach(function(h,i){idx[h]=i+1;});
  if(idx.hasOwnProperty(colName)) {
    return idx[colName];
  }else{
    return -1;
  }
}
user_72150910

Explanation of errors

Exception: The parameters (number,number,number,number) don't match the method

The method signature for getRange method has only four parameters, in that order:

  1. Row to start from
  2. Column to start from
  3. Number of rows to span (optional, defaults to 1)
  4. Number of columns to span (optional, defaults to 1)

The fifths parameter is invalid and the error message clearly states that. What is confusing about the message is that it omits the invalid parameter, but that's on the error handling library used.

Exception: The starting column of the range is too small

On the one hand, your getColIndexByName function (designed to work similar to indexOf() method) has two possible return states: a positive number ("integer" if we had types in JS) or -1 on failure to find the colName passed as an argument.

On the other hand, getRange method accepts only positive numbers, so in case it is passed a number that is <= 0, an error will be thrown. Consider the following example that reproduces the "The starting column of the range is too small" error:

function testOutOfRange() {
  const ss = SpreadsheetApp.openById('yourIdHere');
  const sh = ss.getSheets()[0];
  sh.getRange(1, -1);
}

Note: in V8 runtime, the function (at least for my test) just hangs indefinitely. In Rhino runtime, the "desired" error is thrown.

Since you use the getColIndexByName without any guards, the following line blows up if it fails to find the Notes column (I assume this is the case because the call works for other column names):

body += "\n\nNotes: " + sheet.getRange(row, getColIndexByName("Notes")).getValue();

Optimizations

Your getColIndexByName could be reduced down to (note that it would return 0 on no match and still blow up the sendout):

function getColIndexByName(colName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var numColumns = sheet.getLastColumn();
  var row = sheet.getRange(1, 1, 1, numColumns).getValues()[0];
  return row.indexOf(colName) + 1;
}

Applied to the question

If the assumption that for other column names the script works is true, then the getColIndexByName fails to find the "Name" column, and given the lack of guards the emailStatusUpdates script fails due to parameter constraints violation.

If that's not true, the answer will be updated

user_114076950
Related