Ошибка exception the starting row of the range is too small - Ремонт и установка крупной бытовой техники

I am currently getting an error with my google app script code which it is saying:

The starting row of the range is too small. (line 48, file «sendEmail»)

I am not to sure why it is to small. The rows go up to M where the «sent» string value is being placed.

here is my code:

function myFunction(e) {
  
  var sheet = SpreadsheetApp.openById("1naSWINA8_uxeLUsj0lFntqILyDj2nirb56uvkBel79Y").getSheetByName("CRM Feedback");
  
  var ss = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");
  
  var data = sheet.getRange(4, 1, ss.getLastRow(), ss.getLastColumn()).getValues();
  
  var manager_email = "thomas.hunt@careertrackers.org.au";
  
  for( var i = 0; i< data.length;i++ ) {
    
    var timestamp =             data[i][0];
    var requesterEmail =        data[i][1];
    var starRating =            data[i][2];
    var requestCatergory =      data[i][3];
    var description =           data[i][4];
    var label =                 data[i][5];
    var ticketId =              data[i][6];
    var comment =               data[i][7];
    var status =                data[i][8];
    var priority =              data[i][9];
    var office =                data[i][10];
    
    var check = data[i][11];
    Logger.log(check)
    
    var checkTimestamp = data[i][0]
    Logger.log(checkTimestamp)
    
    
    if(check == false){
      continue;
    } else {
      
      
      var subject = "Weekly Timesheet - ";
      var body = "<body>";
      body += "<div>Hi " + comment
      body += "</body>";
      
      
      
      MailApp.sendEmail(manager_email, subject, body, {htmlBody:body}) 
      
          var sent_string = "sent";
    //error here
    ss.getRange(i, 12).setValue(sent_string)
    
    if (sent_string){
      return
      
    }
    
    
    
    

    }
    
    
  }
  
  
  
  
}

asked Oct 3, 2019 at 2:34

From the documentation, the row parameter for Sheet.getRange accepts an Integer where the row index starts with 1.

In the for loop, you are passing i as the row and i starts with 0, therefore it is throwing the range is too small error. You should be passing i + 1 instead of i as the row parameter.

for( var i = 0; i< data.length;i++ ) {
    // code here

    ss.getRange(i + 1, 12).setValue(sent_string)

    // code here
}

answered Oct 3, 2019 at 3:07

Thum Choon TatThum Choon Tat

3,0841 gold badge21 silver badges24 bronze badges

Источник

I have this little script that’s giving me some trouble. I get the following error:

Error Exception: The starting row of the range is too small
appendToRecords @ Code.gs:10

What I’m trying to accomplish is to assign cell values(A list of names) from a specific range on one sheet after the last row of a specific column (F) in another sheet.

This is my current code:

function appendToRecords() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcesheet = ss.getSheetByName("Assignment");
  var targetSheet = ss.getSheetByName("Review")
  var reportData = sourcesheet.getRange("C4:C13")
                              .getValues();
  var owner = targetSheet.getRange("F1:F").getValues();
  var lastRow = owner.filter(String).lenght;
  //copy data
  targetSheet.getRange(lastRow + 1,1,10,1)
              .setValues(reportData);

};

Any help would be greatly appreciated!

asked Apr 6, 2022 at 21:48

There are several ways to solve this, depending on the details of your data.

The quick fix is to correct the typo in lenght. That will not work correctly, though, when column Review!F1:F contains blank values in between other values.

The better quick fix is to use Sheet.appendRow(), in a forEach loop.

The fix I would recommend, however, would be to use the the appendRows_() utility function.

answered Apr 7, 2022 at 9:38

doubleunarydoubleunary

11.5k2 gold badges13 silver badges43 bronze badges

Источник

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...
}

Источник

Problem Description:

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 class="form-row">
      <div class="form-group col-md-2">  
        <label for="idNum">ID Number</label>
        <input type="text" class="form-control" id="idNum">
        <output id="rnum"></output>
      </div>
     <button type="button" class="btn btn-outline-info" 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      |

Solution – 1

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.

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...
}

Источник

Exception: The starting row of the range is too small — Logs Numbers Why

Questions : Exception: The starting row of the range is too small — Logs Numbers Why

2023-06-21T10:16:12+00:00 2023-06-21T10:16:12+00:00

680

I have a basic script to take numbers from a solved ourcodings google-apps-script sheet and use them to create a range, as solved ourcodings google-apps-script well as using the last column function. I solved ourcodings google-apps-script have had the error range is too small for solved ourcodings google-apps-script the posting range.

When I log the output for both the column solved ourcodings google-apps-script and row numbers these come out as expected!

I thought initially, it was because one was solved ourcodings google-apps-script a last column pull and the other was pulling solved ourcodings google-apps-script an integer from the cell in the sheets, as solved ourcodings google-apps-script they were coming with decimal places, so I solved ourcodings google-apps-script have overcome this with the conversion to solved ourcodings google-apps-script number and then removing the decimals with solved ourcodings google-apps-script the .tofixed() but this does not work solved ourcodings google-apps-script either. Any ideas?

 function weeklyData() {
  var sourcess = SpreadsheetApp.openById('1B93Oq2s9Nou5hVgOb3y3t15t9xnqRMBnrYkAed-oxrE');  // key of source spreadsheet
  var sourceSheet = sourcess.getSheetByName('Measures & Answers');  // source sheet name - change to your actual sheet name
  var lr = Number(sourceSheet.getRange(2,3).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow()).toFixed(0);

for(var i=0;i<lr+1;i++){ 
  var dataValue = sourceSheet.getRange(i+2,3).getValue(); //This weeks numbers to update into table

  var rowdataRange = sourceSheet.getRange(i+2,4).getValue(); //The row that the data needs to be pasted
  var rowformat = Number(rowdataRange);
  var row = rowformat.toFixed(0);

  var pasteSheet = sourcess.getSheetByName('WHERE DATA ENDS UP');  // Data is to be pasted - change to your actual sheet name
  var pasteColumn = pasteSheet.getRange(12,12).getDataRegion(SpreadsheetApp.Dimension.COLUMNS).getLastColumn()+1;
  var column = pasteColumn.toFixed(0); // Column that is free for this weeks data

  var pasteRange = pasteSheet.getRange(row,column,1,1);

 Logger.log(pasteRange);

//  pasteRange.setValue(dataValue);

  }};

Total Answers 2

Answers 1 : of Exception: The starting row of the range is too small — Logs Numbers Why

Your script works fine for me. I suspect questions ourcodings exception this is an example script you’ve adapted questions ourcodings exception from somewhere and trying to apply it to questions ourcodings exception your data structure?

The reason you are getting the error is questions ourcodings exception probably because the data in column 4 of questions ourcodings exception your source sheet is not of number questions ourcodings exception format? Either change your data or questions ourcodings exception change the following line to the column questions ourcodings exception containing numeric values.

var rowdataRange = sourceSheet.getRange(i+2,4).getValue();

This script is poorly written for this questions ourcodings exception particular use.

2023-06-21T10:16:12+00:00 2023-06-21T10:16:12+00:00Answer Link

mRahman

Answers 2 : of Exception: The starting row of the range is too small — Logs Numbers Why

You might want to check your Spreadsheet questions ourcodings exception since it has lots of random values at questions ourcodings exception random ranges.

When you use the following command questions ourcodings exception Logger.log(pasteSheet.getLastColumn()); questions ourcodings exception the number returned is 3753: which means questions ourcodings exception that that is the next available column questions ourcodings exception at which your data will be pasted.

The error message you were getting is questions ourcodings exception due to the fact that the range was questions ourcodings exception incorrect and you were passing wrong questions ourcodings exception values in order to access it, which was questions ourcodings exception most likely because of the values questions ourcodings exception mentioned above.

Moreover, after cleaning all the questions ourcodings exception unnecessary data, you can make use of questions ourcodings exception the below script.

Snippet

function weeklyData() {
    var spreadsheet = SpreadsheetApp.openById("ID_OF_THE_SPREADSHEET");
    var sourceSheet = spreadsheet.getSheetByName("Measures & Answers");
    var pasteSheet = spreadsheet.getSheetByName("WHERE DATA ENDS UP");

    var valsToCopy = sourceSheet.getRange(2, 3, sourceSheet.getLastRow(), 1).getValues();
    var rowsAt = sourceSheet.getRange(2, 4, sourceSheet.getLastRow(), 1).getValues();
    var column = pasteSheet.getRange(12,12).getDataRegion(SpreadsheetApp.Dimension.COLUMNS).getLastColumn()+1;  

    for (var i = 0; i < valsToCopy.length; i++) 
        if (rowsAt[i][0] != "") 
            pasteSheet.getRange(parseInt(rowsAt[i][0]), parseInt(column)).setValue(valsToCopy[i][0].toString());

}

Explanation

The above script gathers all the data questions ourcodings exception that needs to be copied as well as the questions ourcodings exception rows associated with it. In order to questions ourcodings exception make sure you don’t end up using questions ourcodings exception inappropriate values for the ranges, an questions ourcodings exception if condition has been placed to make questions ourcodings exception sure the value is not empty.

Reference

Sheet Class Apps Script — questions ourcodings exception getLastColumn();
Sheet Class Apps Script — getRange(row, questions ourcodings exception column, numRows, numColumns);

2023-06-21T10:16:12+00:00 2023-06-21T10:16:12+00:00Answer Link

joya

Источник