Javascript pagination JSON data from google sheet

Question

Here I am fetching some JSON date from a google sheet as you can tell, I addition I would like this data to be sorted on pages containing only three records per page using pagination, with prev/next functions that will update dynamical without the page reloading.

At this moment I am stuck trying to connect the JSON data to the pagination functions to determine the data length and sort the records in multiple pages.

How can I make the JSON data sort it selves in multiple pages, while navigating between the pages using next/prev?

var url = "https://spreadsheets.google.com/feeds/list/1xo6dUfcVOwPA5Lkd8pKevLj6lP-0gOaPXNBZk4jyuGw/od6/public/values?alt\u003djson";

var perPage = 3;
var currentPage = 1;

$.getJSON(url, function(data) {
    var output = "";
    var length = data.length;
    
    $.each(data.feed.entry, function(index,value) {
       output += "Category: " + value.gsx$largeimage.$t + "</br>"+
                 "Name: " + value.gsx$imagetitle.$t + "</br></br>";
    });
    $(".test").append(output);
});

function prevPage()
{
    if (currentPage > 1) {
        currentPage--;
        changePage(currentPage);
    }
}

function nextPage()
{
    if (currentPage < numPages()) {
        currentPage++;
        changePage(currentPage);
    }
}

function changePage(page)
{
  // ??
}

function numPages()
{
    return Math.ceil(length / perPage);
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<div class="test"></div>
<a href="javascript:prevPage()" id="btn_prev">Prev</a>
<a href="javascript:nextPage()" id="btn_next">Next</a>
page: <span id="page"></span>

You could use the Google Sheet API, i think here developers.google.com/sheets/api/reference/rest/v4/… is an example of using a range, perhaps this way you could easily adopt your pagination logic to the request. You could try it out the API on that link based on spreadsheetId and some other initial information. — user2560539
– user2560539, Commented Mar 31, 2019 at 0:23

Tanaike · Accepted Answer · 2019-03-30 23:48:04Z

You want to turn the page of data retrieved from Spreadsheet every perPage.

If my understanding is correct, how about this modification? I think that there are several methods for your situation. So please think of this as just one of them.

The flow of this modified script is as follows.

Retrieve all data from Spreadsheet. At that time, 1st paget is displayed.
When "Next" or "Prev" is clicked, the content of <div class="test"></div> is cleared.
Put the next page using the retrieved data from Spreadsheet and currentPage.

Modified script:

var url = "https://spreadsheets.google.com/feeds/list/1xo6dUfcVOwPA5Lkd8pKevLj6lP-0gOaPXNBZk4jyuGw/od6/public/values?alt\u003djson";

var perPage = 3;
var currentPage = 1;
var values = []; // Added

// Modified
$.getJSON(url, function(data) {
  var output = "";
  $.each(data.feed.entry, function(index,value) {
    values.push({Category: value.gsx$largeimage.$t, Name: value.gsx$imagetitle.$t});
    if (index < perPage) {
      output += "Category: " + value.gsx$largeimage.$t + "</br>"+
                "Name: " + value.gsx$imagetitle.$t + "</br></br>";
    }
  });
  $(".test").append(output);
});

function prevPage() {
  if (currentPage > 1) {
    currentPage--;
    changePage();
  }
}

function nextPage() {
  if (currentPage < numPages()) {
    currentPage++;
    changePage();
  }
}

// Modified
function changePage() {
  $(".test").html("");
  var start = (currentPage - 1) * perPage;
  var output = values.slice(start, start + perPage).reduce((s, e) => {
    return s += "Category: " + e.Category + "</br>"+
                "Name: " + e.Name + "</br></br>";    
  }, "");
  $(".test").append(output);
}

// Modified
function numPages() {
  return Math.ceil(values.length / perPage);
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<div class="test"></div>
<a onclick="prevPage()" href="#" id="btn_prev">Prev</a> <!-- Modified -->
<a onclick="nextPage()" href="#" id="btn_next">Next</a> <!-- Modified -->
page: <span id="page"></span>

If I misunderstood your question and this was not the result you want, I apologize.

Collectives™ on Stack Overflow

Javascript pagination JSON data from google sheet

1 Answer 1

Modified script:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

Comments

Your Answer

Sign up or log in

Post as a guest

Related