Labels

Learn the powerful enterprise adaptable database:

Getting Started With ADABAS & Natural

Friday, February 3, 2017

HTML5 Upload Form App Script Google Sheet


.
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.

.

2 comments:

  1. 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

    ReplyDelete
    Replies
    1. try this, http://codepen.io/notarazi/pen/oZmgEB

      Delete