Drag/Drop/Sort to Google Doc Creation

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.

Screen Shot 2016-07-08 at 9.49.21 AM
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.

Comments on this post

  1. Tom Woodward said on July 10, 2016 at 2:14 pm

    Forgot to add the trigger . . . stupid me.

Leave a Reply

Trackbacks and Pingbacks on this post

No trackbacks.

TrackBack URL