Exception: The starting row of the range is too small. | Google Apps Script error-CodePudding

When I test on the HTML page, it has no response after entering ID Number and clicking search button.

Could anyone help me how to change the codes to make it work?

Below codes are trying to display a HTML page for users to input ID Number and then get the latest row of related data from Google Sheet. Thank you.

index.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div >
      <div >  
        <label for="idNum">ID Number</label>
        <input type="text"  id="idNum">
        <output id="rnum"></output>
      </div>
     <button type="button"  id="searchbtn">Search Profile</button>
   </div>
<script>
    document.getElementById("searchbtn").addEventListener("click", searchProfile);
   function searchProfile(){
     var appProfile = document.getElementById("idNum").value;
     if(appProfile.length === 6){
     google.script.run.withSuccessHandler(updateProfile).updateIdNum(appProfile);
     } 
     //else{
     //alert("invalid Profile Number");
     }   
   function updateProfile(returnData){
      document.getElementById("rnum").value = returnData[1];
   }
    </script>
  </body>
</html>

Apps Script:

function doGet(e){
  return HtmlService.createHtmlOutputFromFile("index");
}

function updateIdNum(appProfile){
  //  var appProfile = "101018"

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ws = ss.getSheetByName("Results");

let data = ws.getRange(2, 1, ws.getLastRow(), 1).getValues() // retrieve the first column, but not the header! and remove all possible empty cells
  .flat() // transform the array from [[...],[...]] to [...,...]
  .filter(cell => cell != '') // remove empty rows
let myPnum = data.indexOf(appProfile) // search your ID
if(myPnum == -1) {
  // then the ID doesnt exist in the sheet, deal with it
} else {
  // you can retrieve the corresponding row
  let fullRow = ws.getRange(myPnum   2, 1, 1, ws.getLastColumn()).getValues();
  // do whatever you need to do...
}

  if (myPnum > -1){
    return dataRow[0];
  } else {
    Logger.log("unknown")
    //return "unavailable";
  } 
}


function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
  .createMenu('Custom Menu')
  .addItem('Show sidebar', 'showSidebar')
  .addToUi();
}

function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('index')
  .setTitle('My custom sidebar')
  .setWidth(300);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
  .showSidebar(html);
}

Google Sheet - Sheet (Result) look like:

ID Number | First Name | Last Name|
101018    | John       | Doe      |
101011    | Jane       | Doe      |

CodePudding user response：

var myPnum = dJoin.indexOf(appProfile);
var dataRow = ws.getRange(myPnum   1, 1, 1, ws.getLastColumn()).getValues();

You don't handle the case when appProfile is not in the dJoin array.

When this happens, the indexOf will return -1. So myPnum will equals -1. And so the first parameter of getRange will be -1 1 = 0 which is incorrect for Apps Script, it has to be 1 (= first line) or higher.

Retrieve the ID properly from your sheet

myPnum 2 will not help you. The error will disappear but the problem is not there. The problem is how you retrieve the values from the sheet. You should not do join().split().

To retrieve all the IDs already present in your sheet, you need to do

let data = ws.getRange(2, 1, ws.getLastRow(), 1).getValues() // retrieve the first column, but not the header! and remove all possible empty cells
  .flat() // transform the array from [[...],[...]] to [...,...]
  .filter(cell => cell != '') // remove empty rows
let myPnum = data.indexOf(appProfile) // search your ID
if(myPnum == -1) {
  // then the ID doesnt exist in the sheet, deal with it
} else {
  // you can retrieve the corresponding row
  let fullRow = ws.getRange(myPnum   2, 1, 1, ws.getLastColumn()).getValues();
  // do whatever you need to do...
}