Json Google Sheet v3 to v4 migration in Google App Script GAS

Json Google Sheet v3 to v4 migration in Google App Script GAS

abedoyamabedoyam Posts: 5Questions: 0Answers: 0

Error messages shown:

"sAjaxSource": "https://spreadsheets.google.com/feeds/list/spreadsheet_id/tab_ordinal/public/full?alt=json"

Description of problem:
https://cloud.google.com/blog/products/g-suite/migrate-your-apps-use-latest-sheets-api

Solution!:

1) Be sure to have the spreadsheet published:

2) Json url structure for version 4:

var url = 'https://sheets.googleapis.com/v4/spreadsheets/' +
spreadsheet_id + '/values/' + tab_name +
'?alt=json&key=' + api_key;

spreadsheet_id = 1EG55TIiC8rzzFEJJLylNvV7gT365CbLmMRTbjN-W0eIk
api_key = AIzaSyDvn7aeAkSJCdiVM-u0neth9YciN-2I2MT
tab_name = Range or Range!A2:Z

https://sheets.googleapis.com/v4/spreadsheets/1EG55TIiC8rzzFEJJLylNvV7gT365CbLmMRTbjN-W0eIk/values/Range!A2:Z?alt=json&key=AIzaSyDvn7aeAkSJCdiVM-u0neth9YciN-2I2MT;

--- Output successfully ----

How to get the api_key?

A - Go to the Google API Console:
https://console.cloud.google.com/apis/credentials

B - Create a new project:

C - Click Enable API:
Look in the Library section and enable the Google Drive API and the Google Sheets API.

D - Create credentials for a web server to access the application data:

E - Name the service account and grant it an editor role in the project:

https://codepen.io/abedoyam/pen/jOwGWNR

HTML:
<!DOCTYPE html>
<html>
<head>
<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>

    <link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
    <script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>

    <meta charset=utf-8 />
    <title>DataTables - JS Bin</title>
</head>
<body>
    <div class="container">
        <table id="example" class="display" width="100%">
                  <thead>
                    <tr>
                        <th>Col1</th>
                        <th>Col2</th>
                        <th>Col3</th>
                        <th>Col4</th>
                        <th>Col5</th>
                        <th>Col6</th>
                        <th>Col7</th>
                        <th>Col8</th>
                    </tr>
                </thead>
        </table>
    </div>

    <?!= include("datatable_prueba-js"); ?>
</body>

</html>

**JS: **
var spreadsheet_id = "Here you Spreadsheet_Id";
var api_key = "you ApiKey";
var tab_name = "Range!A2:Z";

$(document).ready(function() {
$('#example').DataTable( {
"sAjaxDataProp": "values",
"sAjaxSource": "https://sheets.googleapis.com/v4/spreadsheets/"+spreadsheet_id+"/values/"+tab_name+"?alt=json&key="+api_key,

   "aoColumns": [
         { "bVisible": false }, //Para no mostrar la columna
         {values: ''},
         {values: ''},
         {values: ''},
         { "bVisible": false }, //Para no mostrar la columna
         {values: ''},
         {"mRender": function ( data, type, row ) 
            {
              return data;
            }
         },
         {"mRender": function ( data, type, row ) 
            {
              return row[2] +' '+ row[9];
            }
         }
      ]
} );

} );_

Replies

  • allanallan Posts: 54,572Questions: 1Answers: 8,534 Site admin

    Many thanks for posting this!

    Allan

  • abedoyamabedoyam Posts: 5Questions: 0Answers: 0

    Thank you very much before for such a fabulous tool

Sign In or Register to comment.