No data available in table 50% of the time, Google Sheet 404

oliverater

Link to test case: https://oliverater.github.io/datatable-test/
Debugger code (debug.datatables.net): https://debug.datatables.net/onopuq
Error messages shown: GET https://spreadsheets.google.com/feeds/list/1D8EpqaTT0MqllKCjugcqS2Ej6N97FOw7Ml9H4mRSlno/1/public/full?alt=json 404
Description of problem: jQuery GET finds datasource only 50% of the time. I have used this method in previous datatables and has worked fine. End result will be two datatables in separate iframes on a Wordpress site, if that would have any impact?

HTML:

<div id="table-container">
<table id="ipccObservations" class="table table-hover table-striped table-sm" cellspacing="0" width="100%">
  <div id="header">
  </div>
  <div id="header-clear">
    <div id="header-title">
      <h1>Title</h1>
      <h5>Filter via the search box | <a class="toggle-vis" data-column="3">Toggle special report</a></h5>
    </div>
  </div>
  <thead>
    <tr>
      <th></th>
      <th colspan="2" class="report-head">Fifth assessment report</th>
      <th colspan="2" class="report-head">Special reports</th>
      <th colspan="2" class="report-head">Sixth assessment report</th>
    </tr>
    <tr>
      <th class="th-sm">Topic
      </th>
      <th class="th-sm">Section
      </th>
      <th class="th-sm">Statement
      </th>
      <th class="th-sm">Section
      </th>
      <th class="th-sm">Statement
      </th>
      <th class="th-sm">Section
      </th>
      <th class="th-sm">Statement
      </th>
    </tr>
  </thead>
  <tbody id="table">
  </tbody>
</table>
</div>

JS:

$.getJSON("https://spreadsheets.google.com/feeds/list/1D8EpqaTT0MqllKCjugcqS2Ej6N97FOw7Ml9H4mRSlno/1/public/full?alt=json", function (data) {

      
      var sheetData = data.feed.entry;
  
      var i;
      for (i = 0; i < sheetData.length; i++) {
        var topic = data.feed.entry[i]['gsx$topic']['$t'];
        var section1 = data.feed.entry[i]['gsx$section1']['$t'];
        var statement1 = data.feed.entry[i]['gsx$statement1']['$t'];
        var section2 = data.feed.entry[i]['gsx$section2']['$t'];
        var statement2 = data.feed.entry[i]['gsx$statement2']['$t'];
        var section3 = data.feed.entry[i]['gsx$section3']['$t'];
        var statement3 = data.feed.entry[i]['gsx$statement3']['$t'];
        

        table.row.add([
          topic,
          section1,
          statement1,
          section2,
          statement2,
          section3,
          statement3,
        ]).draw( false );
      }


    });



  const table = $('#ipccObservations').DataTable({

  lengthChange: false,
  ordering: false,
  "scrollY": "600px",
  "scrollCollapse": true,
  "paging": false, 
  language:{
    search: "Search:",
  },

// Add tooltip

   drawCallback: function (settings) {
    $('[data-toggle="tooltip"]').tooltip({
      html: true,
    sanitize: false,
    });

  },




  columnDefs: [
    { className: "border-topic", "targets": [ 1 ] },
    {  className: "reports", targets: [3, 5] },
    { className: "column-border", "targets": "_all" },
    { targets: [2, 4, 6],
    render: function checkPosition(data, type, row, meta) {
    if (window.matchMedia('(min-width: 401px) and (max-width: 767px)').matches) {
        return type === 'display' && data.length > 60 ?
        '<div data-toggle="tooltip" disabled title="'+data+'">'+data.substr( 0, 75 ).replace( '&quot;', '"' )+'...</span>' :
        data;
    } else if (window.matchMedia('(max-width: 400px)').matches){ 
        return type === 'display' && data.length > 50 ?
        '<div data-toggle="tooltip" disabled title="'+data+'">(Click to expand)</span>' :
        data;
    } else if (window.matchMedia('(min-width: 768px)').matches){ 
      return type === 'display' && data.length > 600 ?
        '<div data-toggle="tooltip" disabled title="'+data+'">'+data.substr( 0, 600 )+'...</span>' :
        data;
      }
    }
  }
    ],
    

    });

  $('a.toggle-vis').on( 'click', function (e) {
        e.preventDefault();
 
        // Get the column API object
        var column = table.column(3);
        var column1 = table.column(4);
 
        // Toggle the visibility
        column.visible( ! column.visible() );
        column1.visible( ! column1.visible() );
    } );

I have tried to setup an error handler and retry to get the data but not entirely sure how best to do so...
Anyone have any solutions / ideas? Many thanks!

allan

That is weird! I'd say you would need to get in touch with the server host, but it's Google in this case, and we all know how (not) easy it is get get support from them...

What makes this even odder is that I have a 100% success rate just loading the JSON in my browser directly. The Ajax load though, not so much. The 404 response says:

Sorry, unable to open the file at present.

Then reload another time or two and it loads for Ajax.

I've replicated the issue via cURL as well:

curl -s -o /dev/null -w "%{http_code}\n" 'https://spreadsheets.google.com/feeds/list/1D8EpqaTT0MqllKCjugcqS2Ej6N97FOw7Ml9H4mRSlno/1/public/full?alt=json' -H 'User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:91.0) Gecko/20100101 Firefox/91.0' -H 'Accept: application/json, text/javascript, */*; q=0.01' -H 'Accept-Language: en-GB,en-US;q=0.7,en;q=0.3' --compressed -H 'Origin: https://oliverater.github.io' -H 'Connection: keep-alive' -H 'Referer: https://oliverater.github.io/' -H 'Sec-Fetch-Dest: empty' -H 'Sec-Fetch-Mode: cors' -H 'Sec-Fetch-Site: cross-site' -H 'Pragma: no-cache' -H 'Cache-Control: no-cache'

It flips (not quite a true toggle) between 200 and 404!

Even with without all the headers the browser adds it has the same behaviour:

curl -s -o /dev/null -w "%{http_code}\n" 'https://spreadsheets.google.com/feeds/list/1D8EpqaTT0MqllKCjugcqS2Ej6N97FOw7Ml9H4mRSlno/1/public/full?alt=json'

So yes, no question in my mind that it is a Google thing I'm sorry to say.

Possibly a rate limiter? Although it doesn't behave like I would expect for a rate limiter (no HTTP headers warning you).

Sorry!
Allan

oliverater

Thanks you @allan for checking this out. You're 100% right it's a Google thing - it looks like the Sheets API is updating to v4, which requires an API key to be declared in the url. There's more info in the responses to my post here: https://support.google.com/docs/thread/121088347/retrieving-data-from-sheets-results-in-404-error-50-of-the-time

I have since tried updating a datatable project with the new API request but now get the error: "Cannot read property 'entry' of undefined"

$.getJSON("https://sheets.googleapis.com/v4/spreadsheets/{SPREADSHEETID}/values/{TABNAME}?alt=json&key={API_KEY}", function (data) {

var sheetData = data.feed.entry;

allan

Weird that it worked sometimes and not others! Perhaps it depended on which specific server in Google it hit...

Regarding the error you are getting now - I'd suggest logging out the JSON you are getting back: console.log(data) and just check that it does have a feed parameter. It sounds like it doesn't.

Allan

abedoyam

Hi Allan,previously, everything worked perfectly in v3... with the new API update on Google Sheet from v3 to v4, I can't find the solution, I currently have the following structure, so it throws an error:

Any solution or guidance in this regard?

kthorngren

@abedoyam The place to start is to follow the troubleshooting steps found at the link in the error.
https://datatables.net/manual/tech-notes/7

Let us know what you find in the XHR response.

Kevin

abedoyam

Hello Datatable Team, I already have the solution in hand, I am documenting the step by step to be clear about the new update of the Json format in Google Sheet in version 4, I will be sharing soon. Happy day

abedoyam

Hello team, here is the solution, please follow the link below:

https://datatables.net/forums/discussion/69822/json-google-sheet-v3-to-v4-migration-in-google-app-script-gas/