I am trying to build an image carousel that uses a Google sheet as the source for the information. I can get the carousel to work if I hard code the JSON, but I am having issues getting the Google Sheet JSON working.
I have opened everything up for viewing that I am working on:
Google Sheet (the script that creates the JSON output can be accessed through Tools/Script Editor)
Google Sheet JSON Output
Google Apps Script Project
Here is the Content Service Web App (generates the JSON from the Google Sheet):
// The name of the spreadsheet from the browser location bar.
// Copy after 'key=' until before the next URL parameter beginning w/&
var SPREADSHEET_ID = '1qolqEsn-hxkRwGfyY1O5ttWB9KzqB7MpgNNF6t3mfvk';
var source = SpreadsheetApp.openById(SPREADSHEET_ID);//Defines the source spreadsheet, in this example, the source is the current spreadsheet.
var source_sheet = source.getSheetByName("Sheet1");//Enter the name between the quotes of the sheet within the specified source spreadsheet that holds the data you want to transfer
var lastRow = source_sheet.getLastRow();//For checking last row with data in cell and formatting range
var lastColumn = source_sheet.getLastColumn();//For checking last row with data in cell and formatting range
var source_range = source_sheet.getRange(1,1,lastRow,lastColumn);//(StartRow,StartColumn,NumberofRowstoGet,NumberofColumnstoGet)
// The name of the sheet, displayed in a tab at the bottom of the spreadsheet.
// Default is 'Sheet1' if it's the first sheet.
var SHEET_NAME = 'Sheet1';
function doGet(request) {
// var callback = request.parameters.jsonp;
var callback = request.parameters.callback;
var json = callback + '(' + Utilities.jsonStringify(source_range.getValues()) + ')';
return ContentService.createTextOutput(json).setMimeType(ContentService.MimeType.JAVASCRIPT);
}
// Testing to see if the jsonp parameter is being used properly.
function testDoGet() {
var request = {parameters: {jsonp: 'callback'}};
var results = doGet(request);
Logger.log(results.getContent());
}
Below is a working code snippet with the hard coded JSON commented out, using the feed from the spreadsheet. Again, the script is fully functional using the hard coded JSON, but does not work when I attempt to use the feed URL.
$(function(){
$.getJSON('https://script.google.com/macros/s/AKfycbzpOcYAX9CrBxGKyQ18Nupe9EzGU-Byl7_A-fY-TSd1zbcvAw/exec?callback=?',
function(json) {
// console.log(json); //Testing
// console.log(json[0][0]); //Testing
var mhtml = '<ul>';
for (var i = 1; i < json.length; i++){ //For each item in the json array
// console.log('image: ' + json[i][0]); //Log the array field
// console.log('title: ' + json[i][1]); //Log the array field
// console.log('desc: ' + json[i][2]); //Log the array field
mhtml += '<li><div class="img-responsive"><img src="https://images1-focus-opensocial.googleusercontent.com/gadgets/proxy?url='+json[i][0]+'&container=focus&resize_w=665" /></div>';
mhtml += '<div class="desc"><h1 class="title">'+json[i][1]+'</h1>';
mhtml += '<p class="expert">'+json[i][2]+'</p></div>';
mhtml += '</li>';
};
mhtml += '</ul>';
$('.slider').append($(mhtml));
slideshow();
});
function slideshow() {
// console.log('running slideshow'); //Testing
// console.log('slider length: ' + $(".slider ul> li").length); //Testing
$(".slider ul> li:gt(0)").css("opacity" , "0");
var j = 0;
var delay = 3000; //millisecond delay between cycles
function cycleThru(){
var jmax = $(".slider ul> li").length -1;
$(".slider ul> li:eq(" + j + ")")
.animate({"opacity" : "1.0"} ,400)
.animate({"opacity" : "1"}, delay)
.animate({"opacity" : "0"}, 400,
function(){
(j == jmax) ? j=0 : j++;
cycleThru();
});
};
cycleThru();
}
});@import url(https://fonts.googleapis.com/css?family=Yanone+Kaffeesatz:400,700);
*{
margin: 0;
padding: 0;
font-family: 'Yanone Kaffeesatz';
}
h1 {
font-size: 3em;
font-family: arial;
}
p {
font-size: 1em;
font-family: arial;
}
ul li { margin: 0;
padding: 0;list-style:none}
.slider {
display: block;
min-height: 345px;
max-width: 665px;
margin: auto;
border: 12px rgba(255,255,240,1) solid;
-webkit-box-shadow: 0px 0px 5px rgba(0,0,0,.8);
-moz-box-shadow: 0px 0px 5px rgba(0,0,0,.8);
box-shadow: 0px 0px 5px rgba(0,0,0,.8);
margin-top: 20px;
position: relative;
}
.slider ul{
display: block;
height: auto;
max-width: 640px;
overflow:hidden}
.slider>ul>li {
height: 320px;
float: left;
position: absolute;
}
.slider >ul>li>img {
margin: auto;
height: 100%;
min-height: 345px;
max-width: 665px;
}
.desc {
position: absolute;
bottom: 0;
left: 0;
width: 260px;
z-index: 1000;
background-color: rgba(255,255,240,0.5);
display: inline-block;
text-align: center;
padding-top: 7px;
border-top-right-radius: 15px;
border-bottom-right-radius: 15px;
}<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script src="https://storage.googleapis.com/code.getmdl.io/1.0.4/material.min.js"></script>
<?!= include('css'); ?>
<meta name="viewport" content="initial-scale=1, maximum-scale=1, user-scalable=no, width=device-width" />
</head>
<body>
<div class="slider"></div>
</body>
</html>You need to add the callback parameter to your URL.
$.getJSON('https://script.google.com/macros/s/AKfycbzpOcYAX9CrBxGKyQ18Nupe9EzGU-Byl7_A-fY-TSd1zbcvAw/exec?callback=?', ...
In your contentservice app:
function doGet(request) {
var callback = request.parameters.callback;
var json = callback + '(' + Utilities.jsonStringify(source_range.getValues()) + ')';
return ContentService.createTextOutput(json).setMimeType(ContentService.MimeType.JAVASCRIPT);
}