How to add pagination parameter in Google Script

user_14146405 0
Viewed 371

I am trying to use Google Script to parse Google Sheets as JSON. But the problem is data shows all in one time

https://script.googleusercontent.com/macros/echo?user_content_key=wuq_9LI3vgIatlNAP4hjntylItBcH61LXYXvFpbC5h_um48YgvDXJOZdI2Sbwn2-leov9vWqBZxfs0iksGahfK1YyUi4kexzm5_BxDlH2jW0nuo2oDemN9CCS2h10ox_1xSncGQajx_ryfhECjZEnPRXzlSQpAo7NiD1uFvfd55kniGZfmcNCMpPtY_76TwXPVOG9f0Yl_uIsvXmrtpvNgmu1XJIo47X&lib=MpD4s_Dhs12GOOkdpgVYeR4F8VEIE0aCI

how to create a parameter URL like ?page=1 and limit=10 per page?

This my code:

function doGet(e){

 // url
 var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/10S8Igzt1tpqUBgJHVKpny-2s6G3Y3-vFsLMvlZVqpkc/edit#gid=810612111");

 var sheet = ss.getSheetByName("list_film");
 return getUsers(sheet); 
}
function getUsers(sheet){
  var jo = {};
  var dataArray = [];
  var rows = sheet.getRange(5, 1, sheet.getLastRow()-1, sheet.getLastColumn()).getValues();
  var rows = sheet.getDataRange().getValues();
  
  for(var i = 0, l= rows.length; i<l ; i++){
    var dataRow = rows[i];
    var record = {};
    record['id'] = dataRow[0];
    record['year'] = dataRow[1];
    record['title'] = dataRow[2];
    record['img'] = dataRow[3];
    
    dataArray.push(record);
    
  }  
  
  jo.user = dataArray;
  var result = JSON.stringify(jo);
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
  
}
google-apps-script
1 Answers

I believe your goal as follows.

  • You want to return the values from row 1 to row 10 by requesting with the query parameter of ?page=1&limit=10 to the Web Apps URL using Google Apps Script.
  • In your script, you want to return the values like {"user":[{"id":"###","year":"###","title":"###","img":"###"},,,]}.

Modification points:

  • In your script,
    • The query parameter is not retrieved.
    • The page number and limit are not declared.
    • var rows = sheet.getRange(5, 1, sheet.getLastRow()-1, sheet.getLastColumn()).getValues(); is overwritten by var rows = sheet.getDataRange().getValues();.
    • I think that these are the reason of your issue of But the problem is data shows all in one time.
  • In order to retrieve the query parameter, you can use the event object of e of doGet(e).

When above points are reflected to your script, it becomes as follows.

Modified script:

Please copy and paste the following script to the script editor. And please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.

function doGet(e) {
  var page = e.parameter.page || 1;
  var limit = e.parameter.limit || 10;
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/10S8Igzt1tpqUBgJHVKpny-2s6G3Y3-vFsLMvlZVqpkc/edit#gid=810612111");
  var sheet = ss.getSheetByName("list_film");
  return getUsers(sheet, page, limit); 
}

function getUsers(sheet, page, limit){
  var rows = sheet.getDataRange().getValues();
  var dataArray = rows.splice(limit * (page - 1), limit).reduce((ar, [a, b, c, d]) => ar.concat({id: a, year: b, title: c, img: d}), []);
  var jo = {};
  jo.user = dataArray;
  var result = JSON.stringify(jo);
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}
  • In this modification, when you access to https://script.google.com/macros/s/###/exec?page=1&limit=10, the values from row 1 to row 10 are returned. When you access to https://script.google.com/macros/s/###/exec?page=3&limit=10, the values from row 21 to row 30 are returned.
  • When you access to https://script.google.com/macros/s/###/exec, the values from row 1 to row 10 are returned.

References:

user_71086530
Related