Json Google Sheet v3 to v4 migration in Google App Script GAS
Json Google Sheet v3 to v4 migration in Google App Script GAS
abedoyam
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
--- 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
Many thanks for posting this!
Allan
Thank you very much before for such a fabulous tool