In the previous chapter, you learned to create an RSS/Atom feed reader, stock quote ticker, language translator, and to create a document reviewing and commenting application.
In this chapter, you will learn:
To create web applications that return Sheet data as HTML, JSON, and PDF
To send HTTP/HTTPS request with the query string
To create an RSS feed
To create a file upload application
To create a timesheet application
We will create an application to return Sheet data as HTML in the browser. Create a Sheet, rename it as Data, and populate it with some test data as shown in the next screenshot. You can populate the Sheet with any random data with the three columns named First Name, Last Name, and Full Name:
In the Code.gs file, create the doGet function as shown here:
function doGet() {
/*
* This spreadsheet may not be active while this function
* executes, so you cannot get access to active spreadsheet,
* use open by id.
*
*/
var ss = SpreadsheetApp
.openById("Replace with this spreadsheet id");
var SheetData = ss.getSheetByName("Data");
var data = SheetData.getDataRange().getValues();
var html = '<!DOCTYPE html><html><body><table border=1>';
// Each row data passed as argument to the anonymous function.
data.forEach(function(row){
html += '<tr>';
html += '<td>' + row...Now, we will see how to return JSON string instead of HTML content. In the Data Sheet, add another column named DOB as shown here:
Create the doGet
function as shown here:
function doGet(){
/*
* This spreadsheet may not be active while this function
* executes, so you cannot get access to active spreadsheet,
* use open by id.
*
*/
var ss = SpreadsheetApp
.openById("Replace with this spreadsheet id");
var SheetData = ss.getSheetByName("Data");
var data = SheetData.getDataRange().getValues();
// Remove header
data.shift();
var date = new Date();
var currYear = date.getFullYear();
var output = {};
data.forEach(function(row){
var dob = new Date(row[3]);
var dobYear = dob.getFullYear();
/*
* Create full name property within output object.
* Again the full name property is an object.
*
*/
output[row[2]] = {};
/*
* Assign DOB property to full name object...You can create an application to convert Sheet data into a PDF file and store it in Drive, and return the PDF file's URL to the user:
In the Code.gs file, create the doGet function as listed here:
function doGet() {
/*
* This spreadsheet may not be active while this function
* executes, so you cannot get access to active spreadsheet,
* use open by id.
*
*/
var ss = SpreadsheetApp.openById("[[ this spreadsheet id ]]");
var SheetData = ss.getSheetByName("Data");
var template = HtmlService
.createTemplateFromFile("Template.html");
// Assign 'data' to the template object
template.data = SheetData.getDataRange().getValues();
// Evaluate template object as html content
var html = template.evaluate();
// Convert html content to pdf
// var pdf = html.getAs("application/pdf")
// .setName("Test_Data.pdf");
// Or use this code
var pdf = html.getAs(MimeType.PDF).setName("Test_Data.pdf");
// Create...You can send an HTTP/HTTPS request along with the query string. To do this, append the published URL with your query string.
For example: https://script.google.com/macros/s/AKfycbxa4ErKHiX_0gQ0JUU-Q1qMhvRrOsrx3HXuVZp7pzX8UVxMu4w/exec?fname=John
function doGet(e){
Logger.log(e);
}A sample of the logged HTTP/HTTPS request's event object is shown here:
The doGet function listed in the following code snippet shows how you can use the event object to get the required parameters for further processing:
function doGet(e){
// Get the fname value from the query string.
var firstName = e.parameter.fname;
/*
* There is no active spreadsheet, so you should open by id.
* Use the id of the spreadsheet in which your script resides.
*
*/
var ss = SpreadsheetApp.openById("Replace spreadsheet id");
var SheetData = ss.getSheetByName("Data");
var data = SheetData.getDataRange().getValues();
// Remove header
data.shift...You created an RSS reader application in Chapter 6, Creating Feed Reader and Translator Applications. Now, you can create an application to publish an RSS feed. Put the RSS data in a Sheet as shown here:
Also, edit/enter the following doGet function:
function doGet() {
/*
* There is no active spreadsheet, so you should open by id.
* Use the id of the spreadsheet in which your script resides.
*
*/
var ss = SpreadsheetApp.openById([[ this spreadsheet id ]]);
var SheetRss = ss.getSheetByName("RSS Data");
var rssData = SheetRss.getDataRange().getValues();
// Remove header.
rssData.shift();
var strRss = '<?xml version="1.0" encoding="UTF-8"?>';
// Root element.
strRss += '<rss>';
// Open channel element.
strRss += '<channel>';
// Add description and language elements.
strRss += '<description>A brief description of the channel</description>';
strRss += '<language>...You can create an application to upload any file to Drive from the browser. Create the doGet and uploadFiles functions in the Code.gs file as listed here:
In the Code.gs file, add this code:
function doGet() {
// Let's return html page created from the Form.html file.
return HtmlService.createHtmlOutputFromFile('Form.html')
.setTitle("File Upload");
};
function uploadFiles(form) {
// You can change the folder name as you like.
var folderName = "Uploaded Files";
var folder, folders = DriveApp.getFoldersByName(folderName);
// folders is an iterator.
if (folders.hasNext()) folder = folders.next();
// Let's create a folder if it does not exist.
else folder = DriveApp.createFolder(folderName);
// Let's create the file, got from the form, within the folder.
var file = folder.createFile(form.file);
// Let's return the file's url
return file.getUrl();
}The uploadFiles function looks for an existing folder with the name Uploaded...
From the knowledge and experience gathered by creating the preceding applications, you can create this full blown timesheet application. This application can be used in an organization or company to log employees, worked hours in a day or shift. The daily attendance data will be backed in the Backup Sheet for future reference.
Create a new spreadsheet with a Sheet named EmployeesList and populate it with employee names. All these names will be listed as a dropdown automatically in the user interface.
Create another Sheet named TimeSheet and arrange the column headers as shown in the following screenshot. Ensure columns C, D, E, and F are formatted as date, otherwise date may be shown as epoch number. Leave column A blank as it will be used by the script to mark the status of a shift such as sb (shift begin), bb (break begin), be (break end), and se (shift end).
Create another Sheet with the name Backup, which is used to back up every day's shift data...
© 2016 Packt Publishing Limited All Rights Reserved
