This is an experiment focused on getting content chunks from a Google Spreadsheet into a drag/drop web interface for manipulation then back to a Google Form (could be skipped but I did left it so people could add/alter other form elements as desired) and then spitting out the final product as a Google Document.
The image above essentially shows the flow. It’s all done based on the one spreadsheet which has three sheets- one for the content from the form, one for the drag/drop stuff, and one for some additional content to throw into the final document.
It sounds more complicated than it is . . . although it could be simplified. There is interesting potential here to create some really unique experiences without a lot of programming knowledge.
Drag/Drop/Sort
I looked around a bit and ended up settling on a really slick js library called Sortable.
The page consists of a couple of different elements. The HTML is straight forward.
<div class="form-group">
<label for="districtName">A Name</label>
<input type="text" class="form-control" id="districtName" placeholder="District Name">
</div>
<h3>Bacon Related</h3>
<ul id="container">
</ul>
<h3>Animals</h3>
<ul id="containerTwo">
</ul>
</div>
<div class="col-md-8 drop">
<ul id="drop">
<li></li>
</ul>
</div>
This gives me a text field, and three divs – bacon related, animals, and a drop zone. The spreadsheet stuff will be added to the first two and the human will be able to drag and drop them onto the drop zone (and then sort them as well).
var spreadsheetID = "13Eg9kQ1oiOysX72XlkmzxG4L_mi_Jdq-sKf4pVI8pAc";
var url = "https://spreadsheets.google.com/feeds/list/" + spreadsheetID + "/2/public/values?alt=json";//gets the json from the 2nd sheet
$.getJSON(url, function(data) {
var entry = data.feed.entry;
$(entry).each(function(){
// body is set up here
var title = '<li><button type="button" class="btn '+this.gsx$type.$t+'" data-toggle="collapse" data-target="#no'+this.gsx$id.$t+'">'+this.gsx$title.$t+'</button><div id="no'+ this.gsx$id.$t +'" class="collapse">'+this.gsx$statement.$t+'</div><div class="myid">'+this.gsx$id.$t+'</div></li>';
//SECOND PORTION - puts it in the various container based on category listed in the ss
if (this.gsx$type.$t === 'Global'){
$('#container').append(title);
}else {
$('#containerTwo').append(title);
}
});
});
This chunk of javascript gets the json from the second sheet in the Google spreadsheet. I did it with jQuery this time rather than my normal Angular path. The HTML uses Bootstrap’s built in collapse class to allow the content to title the buttons and have the details show/hide when you click on them.
The second part, adds the content to the two different divs based on how it’s labeled in the spreadsheet.
//make it sortable and create three main elements
Sortable.create(container, {
group: "sorting",
sort: true
});
Sortable.create(containerTwo, {
group: "sorting",
sort: true
});
// sort: false
Sortable.create(drop, {
group: "sorting",
sort: true,
onUpdate: function (evt){
var item = evt.item; // the current dragged HTMLElement
console.log(item);
}
});
This chunk in combination with sortable and the HTML enables the drag/drop/sort functionality.
function grabIt (){
var theDiv = document.getElementById('drop');
var theId = theDiv.querySelectorAll('.myid');
var theContent = theDiv.innerHTML;
var theIdContent = theId.innerHTML;
var theDivision = document.getElementById('districtName').value;
var urlParams = [];
var i;
for (i = 0; i < theId.length; i++) {
urlParams.push(theId[i].innerHTML);
}
var newUrl = 'https://docs.google.com/forms/d/1czmbSTmynUlRxAzPwKHEmixEnhdvwRQw7CmF2YMIcZI/viewform?entry.532104747='+theDivision+'&entry.909242351='+urlParams.toString();
window.location=newUrl;
}
This is the function that executes when you click the “Next” button. There are some other ways to do this but what I did was assign a unique ID to each element in the spreadsheet. That element is in the HTML but not displayed. When you click the button, it checks across all the HTML pieces with the div name ‘districtName’ that are within the ‘drop’ div. It then uses those to build an array of IDs. Those ID numbers and the text input are then put into the Google Form URL so that fill in the field automatically. That URL is based on Google’s built in function and would work with Gravity Forms as well.
From there you could add more stuff in the form (or skip it all together).
Once it’s submitted the following Google Script kicks in.
function listFolders() {
//FORM SHEET
var ss = SpreadsheetApp.getActiveSpreadsheet(); //get form spreadsheet
var sheet = ss.getSheets()[0];
// This logs the value in the very last cell of the sheet you used when making the script
var lastRow = sheet.getLastRow();
var dateCell = sheet.getRange(lastRow, 1); //gets date from last row column B
var date = dateCell.getValue();
var districtNameCell = sheet.getRange(lastRow, 2); //gets name from last row column C
var districtName = districtNameCell.getValue();
var mainContentCell = sheet.getRange(lastRow, 3); //gets main text content IDs from last row column D
var mainContentNums = mainContentCell.getValue();
mainContentNums = mainContentNums.split(","); //make it an array
var authorNameCell = sheet.getRange(lastRow, 5); //gets name
var authorName = authorNameCell.getValue();
//TEXT SHEET
var otherSheet = ss.getSheets()[1];//get second sheet
var textLastRow = otherSheet.getLastRow();
//CREATE DOCUMENT
var destFolder = DriveApp.getFolderById('0B_9l84KvUJBWX0pIV3N0d3Y1cUU'); //add document to this folder
var doc = DocumentApp.create(districtName + ' Drag Drop Magic Document ' + date ); //names the document
var docId = DriveApp.getFileById(doc.getId()); //adds the new document to the folder indicated above
destFolder.addFile(docId);
var body = doc.getBody();
body.insertParagraph(0, doc.getName()).setHeading(DocumentApp.ParagraphHeading.HEADING1); //adds document headers
var stockSheet = ss.getSheets()[2];
//COVER PAGE
var getLogo = UrlFetchApp.fetch('https://c2.staticflickr.com/8/7331/27524654473_10ba441c90_z.jpg');//adds an image via URL
var logo = doc.getChild(0).asParagraph().appendInlineImage(getLogo.getBlob());
logo.setWidth(300);
logo.setHeight(300);
var coverCell = stockSheet.getRange(1, 1);
var coverContent = coverCell.getValue(); //gets the chunk of text for the cover sheet from the SS cell A1
body.appendParagraph(coverContent);
body.appendPageBreak();
var introCell = stockSheet.getRange(2, 1);
var introContent = introCell.getValue();
var intro = body.appendParagraph(introContent);
body.appendPageBreak();
//GET THE TEXT PIECES and LOOP through all of them
for (i = 0; i < mainContentNums.length; i++) {
var row = parseInt(mainContentNums[i]); //makes sure it's a number
var titleCell = otherSheet.getRange(row+1, 2); //gets title cell
var titleContent = titleCell.getValue(); //gets title value
var bodyCell = otherSheet.getRange(row+1, 3); //gets body cell
var bodyContent = bodyCell.getValue(); //gets body content
//FILL DOCUMENT
var section = body.appendParagraph(titleContent);
section.setHeading(DocumentApp.ParagraphHeading.HEADING2);
var section = body.appendParagraph(bodyContent);
}
//MAIL MERGE LIKE THING
body.replaceText("<word one>", districtName);
body.replaceText("<prepared by>", authorName);
body.replaceText("<date>", date);
}
That’s pretty well commented up.
If you want to give it a shot. Try it out. The documents should show up below.
Forgot to add the trigger . . . stupid me.