.
HTML5 Upload Form App Script Google Sheet
1) Create html form and javascript codes in Code Editor
forms.html
<!DOCTYPE html>
<html> <head> <base target="_blank"> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <title>Registration</title> <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet"> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/0.97.5/css/materialize.min.css"> <style> .disclaimer{width: 480px; color:#646464;margin:20px auto;padding:0 16px;text-align:center;font:400 12px Roboto,Helvetica,Arial,sans-serif}.disclaimer a{color:#009688}#credit{display:none} </style> </head> <body> <form class="main" id="form" novalidate="novalidate" style="max-width: 480px;margin: 40px auto;"> <div id="forminner"> <div class="row"> <div class="col s12"> <h5 class="center-align teal-text">Registration</h5> <p class="disclaimer">Fill up all fields.</p> </div> </div> <div class="row"> <div class="input-field col s12"> <input id="name" type="text" name="Name" class="validate" required="" aria-required="true"> <label for="name">Name</label> </div> </div> <div class="row"> <div class="input-field col s12"> <input id="pid" type="text" name="Pid" class="validate" required="" aria-required="true"> <label for="pid">Pid</label> </div> </div> <div class="row"> <div class="input-field col s12"> <input id="email" type="email" name="Email" class="validate" required="" aria-required="true"> <label for="email">Email Address</label> </div> </div> <div class="row"> <div class="input-field col s12"> <input id="tel" type="tel" name="Tel" class="validate" required="" aria-required="true"> <label for="tel">Telephone</label> </div> </div> <div class="row"> <div class="input-field col s12"> <select id="position" name="position"> <option value="" disabled selected>Position</option> <option value="Head">Head</option> <option value="Assistant">Assistant</option> <option value="Secretary">Secretary</option> <option value="Others">Others</option> </select> </div> </div> <div class="row"> <div class="file-field input-field col s12"> <div class="btn"> <span>File</span> <input id="files" type="file"> </div> <div class="file-path-wrapper"> <input class="file-path validate" type="text" placeholder="Select a file on your computer"> </div> </div> </div> <div class="row"> <div class="input-field col s6"> <button class="waves-effect waves-light btn submit-btn" type="submit" onclick="submitForm(); return false;">Submit</button> </div> </div> <div class="row"> <div class="input-field col s12" id = "progress"> </div> </div> </div> <div id="success" style="display:none"> <h5 class="left-align teal-text">File Uploaded</h5> <p>Your file has been successfully uploaded.</p> <p class="center-align"><a class="btn btn-large" onclick="restartForm()" >Restart</a></p> </div> </form> <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/0.97.5/js/materialize.min.js"></script> <script> var file, reader = new FileReader(); reader.onloadend = function(e) { if (e.target.error != null) { showError("File " + file.name + " could not be read."); return; } else { google.script.run .withSuccessHandler(showSuccess) .uploadFileToGoogleDrive(e.target.result, file.name, $('input#name').val(), $('input#pid').val(), $('input#email').val(), $('input#tel').val()), $('input#position').val(); } }; function showSuccess(e) { if (e === "OK") { $('#forminner').hide(); $('#success').show(); } else { showError(e); } } function restartForm() { $('#form').trigger("reset"); $('#forminner').show(); $('#success').hide(); $('#progress').html(""); } function submitForm() { var files = $('#files')[0].files; if (files.length === 0) { showError("Please select a file to upload"); return; } file = files[0]; if (file.size > 1024 * 1024 * 5) { showError("The file size should be < 5 MB. "); return; } showMessage("Uploading file.."); reader.readAsDataURL(file); } function showError(e) { $('#progress').addClass('red-text').html(e); } function showMessage(e) { $('#progress').removeClass('red-text').html(e); } $(document).ready(function() { $('select').material_select(); }); </script> </body> </html> |
Code.gs
// 1. Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1";
// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('forms.html').setTitle("Registration With Document");
}
function uploadFileToGoogleDrive(data, file, name, pid, email, tel, position) {
try {
var dropbox = "Received Files";
//var folder, folders = DriveApp.getFoldersByName(dropbox);
var folder=DriveApp.getFolderById('0B86b-ALn-1MGSndHQ2NQMlExNVE');
/*
if (folders.hasNext()) {
folder = folders.next();
} else {
folder = DriveApp.createFolder(dropbox);
}
*/
/* Credit: www.labnol.org/awesome */
var contentType = data.substring(5,data.indexOf(';')),
bytes = Utilities.base64Decode(data.substr(data.indexOf('base64,')+7)),
blob = Utilities.newBlob(bytes, contentType, file),
file = folder.createFolder([name, email].join(" ")).createFile(blob),
filelink=file.getUrl() ;
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
// loop through the header columns
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else if (headers[i] == "name"){
row.push(name);
} else if (headers[i] == "pid"){
row.push(pid);
} else if (headers[i] == "email"){
row.push(email);
} else if (headers[i] == "tel"){
row.push(tel);
} else if (headers[i] == "position"){
row.push(position);
} else if (headers[i] == "filelink"){
row.push(filelink);
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
//return ContentService
// .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
// .setMimeType(ContentService.MimeType.JSON);
return "OK";
} catch (f) {
return f.toString();
} finally { //release lock
lock.releaseLock();
}
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
|
Select menu Publish/Deploy as web app...
Get the script URL.
thanks for this sir.. i have problem with run>setup and run>uploadFileToGoogleDrive. it show that the code cannot run. i have tried test the code, but when i upload the file, it just stuck at 'uploading file' and nothing have to upload to sheet. if you can solve this, much appreciate sir. thanks
ReplyDeletetry this, http://codepen.io/notarazi/pen/oZmgEB
Delete