How to get the specific range of row data from google sheet through apps script

Question

I am trying to fetch some range of data from google sheet via app script.
Let's have an example. I have 96 data rows in google sheet, I want to fetch 10 rows.
From Front-end side the call is based on scrolling event to fetch every next 10 row data.
I have tried below code.

function doGet(request){
  var ss = SpreadsheetApp.openById("MY SHEET ID");
  var sheet = ss.getSheetByName("Products");
  return getUsers(sheet,request); 
}

function getUsers(sheet,request){
  var jo = {};
  var dataArray = [];
  // every time from, where is passed from Frontend, like 2-10, 12-10, 22-10, etc...
  var from = request.parameter.from;
  var where = request.parameter.where;
  // by this if condition, getting data from 1 to 90
  if((sheet.getLastRow()-1)>=(from+where)){
    var rows = sheet.getRange(from,1,from+where, sheet.getLastColumn()).getValues();
    for(var i = 0, l= rows.length; i<l ; i++){
      var dataRow = rows[i];
      var record = {};
      record['ProductId'] = dataRow[0];
      // ...
      // some more column data fetching here
      // ...
      dataArray.push(record);
    }  
    jo.user = dataArray;
    var result = JSON.stringify(jo);
    return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
  }
  // tried to get last 6 data through below code
  else{
    if(from <= (sheet.getLastRow() - 1)){
      var rows = sheet.getRange(from,1,sheet.getLastRow()-1, sheet.getLastColumn()).getValues();
      for(var i = 0, l= rows.length; i<l ; i++){
        var dataRow = rows[i];
        var record = {};
        record['ProductId'] = dataRow[0];
        // ...
        // some more column data fetching here
        // ...
        dataArray.push(record);
      }  
      jo.user = dataArray;
      var result = JSON.stringify(jo);
      return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
    }
  }
}

By this code, I am getting values up to 90 with chunks of 10 rows, but at the end, the last 6 data I am not able to get.
Is there any logic to get those records? I have tried to achieve through last if statement but couldn't get.
Thanks in Advance!

Edit:
as per the solution from Taniake, Getting ProductId column perfectly,
Moreover, If there are multiple columns like ProductId, ProductName, ProductImage, etc..
Range Might be
From

const values = sheet.getRange(from, 1, sheet.getLastRow() - from - where >= 0 ? where : sheet.getLastRow() - from + 1, 1).getValues();

To

const values = sheet.getRange(from, 1, sheet.getLastRow() - from - where >= 0 ? where : sheet.getLastRow() - from + 1, sheet.getLastColumn()).getValues();

how to map those values to JSON constant namely "result" in the solution provided?

Do you need both from and where parameters if you will only ever return 10 results? This can be cleaned up easily enough, but it would be helpful if you'd clarify your motivation and spec. As I rule, I would 1) combine both of these functions into one, 2) just pull in the entire spreadsheet range in question and then slice it as needed with your parameters (this will also eliminate the need for the conditional), 3) use map and reduce rather than loops. This will greatly reduce the odds of fencepost errors and make things a lot easier to read. — Frank

Frank Frank · Accepted Answer · 2020-06-26T05:39:35

Here's a start at a refactor. A few things I don't understand and need clarification on:

Why are you adding from and where for the loop? If I set from=10; where=20, I get results from 10 through 39. I'm guessing that is not what you want.
Obviously you'll want to clean up the default values for myfunc.

function doGet(request)
{
  return myfunc(request.parameter.from, request.parameter.where);
}

function myfunc(from=10, where=20){
  let sheet = SpreadsheetApp.openById("1CP1n-B87RD_BfffrOKVemmbqQ4-rq3W5t59x90tX0VA").getSheetByName("Products");
  var jo = {user:[]};

  const rawdata = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  const data = rawdata.slice(from, where);
  
  data.map((row, idx, arr)=>{
     let record = {};
     record['ProductId'] = row[0];
     jo.user.push(record);
  });

  return JSON.stringify(jo);
}

How to get the specific range of row data from google sheet through apps script

2 Answers

Modified script:

Sample script 1:

Sample script 2:

Note:

References:

Added:

From:

To:

From:

To: