Tuesday, July 13, 2021

Edward Lance Lorilla

Edward Lance Lorilla

【Google App Script GAS】 Multiple Upload and e signature

Posted: 12 Jul 2021 06:42 PM PDT

 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 read_value(request){  

  var ss = SpreadsheetApp.openById("1vSGBziTX1hksq98rKws_EdyDNXTiXkYyrXtnbz29z1Q");
  var sheet = ss.getSheetByName(SHEET_NAME);

  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var json =JSON.stringify(values);

  return json;



}







function uploadFileToGoogleDrive(data, name,  email, tel, position, imgData) {
  try {

    var dropbox = "Received Files";
    //var folder, folders = DriveApp.getFoldersByName(dropbox);
    var folder=DriveApp.getFolderById('13fh8Q3tC0Vz43WqB2LsW6exLlR1zU_uI');
    var file = folder.createFolder([name, email].join(" "))
    var filelink = [];
    for (var index = 0; index < data.length; index++) {
      var contentType = data[index].image.substring(5,data[index].image.indexOf(';')),
      bytes = Utilities.base64Decode( data[index].image.substr( data[index].image.indexOf('base64,')+7)),
      blob = Utilities.newBlob(bytes, contentType,  data[index].name);
      file.createFile(blob)
      filelink.push(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("1vSGBziTX1hksq98rKws_EdyDNXTiXkYyrXtnbz29z1Q");
    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 = [];
    
    var getLastRow = sheet.getRange(sheet.getLastRow(), 3).getValue();

    var rid = (getLastRow == 'pid' ? 0 : getLastRow) + 1;
    // 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(rid);
    } 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);
    }else if (headers[i] == "imgData" && imgData){
      row.push(imgData);
    }

    }
    // 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 update_value(data,pid, name, email, tel, position, imgData){
  try {

    var dropbox = "Received Files";
    //var folder, folders = DriveApp.getFoldersByName(dropbox);
    var folder=DriveApp.getFolderById('13fh8Q3tC0Vz43WqB2LsW6exLlR1zU_uI');
    /*
    if (folders.hasNext()) {
      folder = folders.next();
      } else {
        folder = DriveApp.createFolder(dropbox);
        }
        */
        /* Credit: www.labnol.org/awesome */

        var file = folder.getFoldersByName([name, email].join(" "))
        var filelink = [];
        for (var index = 0; index < data.length; index++) {
          var contentType = data[index].image.substring(5,data[index].image.indexOf(';')),
          bytes = Utilities.base64Decode( data[index].image.substr( data[index].image.indexOf('base64,')+7)),
          blob = Utilities.newBlob(bytes, contentType,  data[index].name);
          file.createFile(blob)
          filelink.push(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("1vSGBziTX1hksq98rKws_EdyDNXTiXkYyrXtnbz29z1Q");
        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] == "email"){
          row.push(email);
        } else if (headers[i] == "pid"){
          row.push(pid);
        }else if (headers[i] == "tel"){
          row.push(tel);
        } else if (headers[i] == "position"){
          row.push(position);
        } else if (headers[i] == "filelink" && filelink){
          row.push(filelink);
        }else if (headers[i] == "imgData" && imgData){
          row.push(imgData);
        }

        }

        var lr = sheet.getLastRow();

        for(var i = 1;i <= lr;i++){
          var rid = sheet.getRange(i, 3).getValue();
          if(rid == pid){
            sheet.getRange(i, 1, 1, row.length).setValues([row]);
            break;
            var result = "value updated successfully";
          }
        }



        return "OK";

  } catch (f) {
    return f.toString();
  } finally { //release lock
  lock.releaseLock();
}

}


function delete_value(pid, name, email, tel, position){
  try {


    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("1vSGBziTX1hksq98rKws_EdyDNXTiXkYyrXtnbz29z1Q");
    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("");
    } else if (headers[i] == "name"){
      row.push("");
    } else if (headers[i] == "email"){
      row.push("");
    } else if (headers[i] == "pid"){
      row.push("");
    }else if (headers[i] == "tel"){
      row.push("");
    } else if (headers[i] == "position"){
      row.push("");
    } 

    }

    var lr = sheet.getLastRow();

    for(var i = 1;i <= lr;i++){
      var rid = sheet.getRange(i, 3).getValue();
      if(rid == pid){
        sheet.deleteRow(i);
        break;
        var result = "value delete successfully";
      }
    }



    // more efficient to set values as [][] array than individually
    //  sheet.getRange(2, 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("1vSGBziTX1hksq98rKws_EdyDNXTiXkYyrXtnbz29z1Q", doc.getId());
}


forms.html



<!DOCTYPE html>
<html>

<head>
  <base target="_blank">
  <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  <title>Registration</title>
  <link rel="stylesheet" href="https://code.jquery.com/mobile/1.3.2/jquery.mobile-1.3.2.min.css" />

  <link rel="stylesheet"
    href="https://fonts.googleapis.com/css?family=Roboto:300,400,500,700,400italic|Material+Icons" />
  <link rel="stylesheet" href="https://unpkg.com/vue-material/dist/vue-material.min.css" />
  <link rel="stylesheet" href="https://unpkg.com/vue-material/dist/theme/default.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>
  <template id="NotFoundPage">
    <div>
      <h1>404 Not Found</h1>
    </div>
  </template>
  <template id="HomePage">


    <md-table v-model="list" md-card>
      <md-table-toolbar>
        <h1 class="md-title">Users</h1>
      </md-table-toolbar>
      <md-progress-spinner v-if="loading" :md-diameter="30" :md-stroke="3" md-mode="indeterminate">
      </md-progress-spinner>
      <md-table-row v-if="!loading" slot="md-table-row" slot-scope="{ item }">
        <md-table-cell md-label="Name" md-sort-by="name">{{ item.name }}</md-table-cell>
        <md-table-cell md-label="Email" md-sort-by="email">{{ item.email }}</md-table-cell>
        <md-table-cell md-label="Job Title" md-sort-by="title">{{ item.position }}</md-table-cell>
        <md-table-cell md-label="Action">
          <md-button class="md-primary" @click="onEdit(item)">Edit</md-button>
          <md-button class="md-accent" @click="onDelete(item, index)">Delete</md-button>
        </md-table-cell>

      </md-table-row>
    </md-table>


  </template>
  <template id="registerpage">
    <div>
      <form class="main" id="form" novalidate="novalidate" style="max-width: 480px;margin: 40px auto;">

        <md-card class="md-layout-item ">
          <md-card-header>
            <div class="md-title">Register</div>
          </md-card-header>

          <md-card-content>


            <md-field>
              <label>Name</label>
              <md-input v-model="register.name"></md-input>
            </md-field>

            <md-field>
              <label>Email</label>
              <md-input type="email" v-model="register.email"></md-input>
            </md-field>
            <md-field>
              <label>Telephone</label>
              <md-input v-model="register.tel"></md-input>
            </md-field>
            <md-field>
              <label>Position</label>
              <md-select v-model="register.position" name="movie" id="movie">
                <md-option value="Assistance">Assistance</md-option>
                <md-option value="Secretary">Secretary</md-option>
                <md-option value="Head">Head</md-option>
                <md-option value="Other">Other</md-option>
              </md-select>
            </md-field>
            <md-field>
              <label>File</label>
              <md-file @change="onFileChange" />
            </md-field>
            <md-field>

              <div :style="{'display': isClose ? 'block' : 'none'}">
                <div ref="page" id="page">
                  <img :src="imgData">
</div>
                </div>
                <div :style="{'display': !isClose ? 'block' : 'none'}" data-role="popup"
                  ref="divPopUpSignContract" id="divPopUpSignContract">
                  <div class="ui-btn-right" @click="closePopUp(false)"> Close </a>
                    <p class="popupHeader">Sign Pad</p>
                  </div>
                  <div class="ui-content popUpHeight">
                    <div ref="div_signcontract" id="div_signcontract">
                      <canvas ref="canvas" id="canvas">Canvas is not supported</canvas>
                      <div>
                        <!-- <input id="btnSubmitSign" type="button" data-inline="true" data-mini="true" data-theme="b" value="Submit Sign" @click="fun_submit()" /> -->
                        <input id="btnClearSign" type="button" data-inline="true" data-mini="true" data-theme="b" value="Clear" @click="init_Sign_Canvas()" />
</div>
                      </div>
                    </div>
                  </div>
            </md-field>
            <md-button v-if="!loading" @click="submitForm" class="md-raised md-primary">Submit</md-button>
            <md-progress-spinner v-else :md-diameter="30" :md-stroke="3" md-mode="indeterminate">
            </md-progress-spinner>

          </md-card-content>


        </md-card>

      </form>
    </div>
  </template>

  <div id="app">
    <div class="page-container">
      <md-app>
        <md-app-toolbar class="md-primary">
          <span class="md-title">Register</span>
          <div class="md-toolbar-section-end">
            <md-button @click="$router.push({path: '/'})">Home</md-button>
            <md-button @click="$router.push({path: '/register'})">Register</md-button>
          </div>
        </md-app-toolbar>
        <nav>
          <div class="nav-wrapper">
            <ul id="nav-mobile" class="left hide-on-med-and-down">
              <li>
                <router-link to="/" exact>Home</router-link>
              </li>
              <li>
                <router-link to="/register">Register</router-link>
              </li>
            </ul>
          </div>
        </nav>
        <md-app-content>
          <router-view></router-view>
        </md-app-content>
      </md-app>
    </div>
  </div>
  <script src="https://code.jquery.com/jquery-1.10.2.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/vue@2.6.12/dist/vue.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/vue-router/3.4.9/vue-router.min.js"></script>
  <script src="https://unpkg.com/vue-material"></script>



  <script>
    Vue.use(VueMaterial.default)
var item = {}
var RegisterPage = Vue.component( 'registerpage', {
template: '#registerpage',
data(){
return{
isClose: false,
imgData: '', 
isSign: false,
leftMButtonDown: false,
loading: false,
redText: false,
process: '',
success: false,
forminner: true,
register:{
imgData: '', 
name: '' ,
pid: '',
email: '',
tel: '',
position: '',
images:[]
}
}
},
mounted(){
this.init_Sign_Canvas();
if(this.$route.params.id){
this.register =  this.$route.params}
},
beforeRouteEnter (to, from, next) {

next(vm => vm.setData(vm.$route.params))

},
methods:{
fun_submit() {
var vm = this;
if(vm.isSign) {
var canvas = $(vm.$refs.canvas).get(0);
vm.imgData = canvas.toDataURL();
$(vm.$refs.page).find('p').remove();
$(vm.$refs.page).find('img').remove();
$(vm.$refs.page).append($('<p>Your Sign:</p>'));
$(vm.$refs.page).append($('<img/>').attr('src',vm.imgData));

vm.closePopUp(false);
} else {
alert('Please sign');
}
},

init_Sign_Canvas() {
var vm = this;
vm.isSign = false;
vm.leftMButtonDown = false;

//Set Canvas width
var sizedWindowWidth =$(vm.$refs.div_signcontract).width();
if(sizedWindowWidth > 700){
sizedWindowWidth = $(window).width() / 2;
}                                                                                                                                                                                                           else if(sizedWindowWidth > 400){
sizedWindowWidth = sizedWindowWidth - 50;
}                                                                                                                                                                                                      else{

sizedWindowWidth = sizedWindowWidth - 20;                                                                                                                                                                                                                                      }


$(vm.$refs.canvas).width(sizedWindowWidth);
$(vm.$refs.canvas).height(200);
$(vm.$refs.canvas).css("border","1px solid #000");

var canvas = $(vm.$refs.canvas).get(0);

canvasContext = canvas.getContext('2d');

if(canvasContext)
{
canvasContext.canvas.width  = sizedWindowWidth;
canvasContext.canvas.height = 200;

canvasContext.fillStyle = "#fff";
canvasContext.fillRect(0,0,sizedWindowWidth,200);

canvasContext.moveTo(50,150);
canvasContext.lineTo(sizedWindowWidth-50,150);
canvasContext.stroke();

canvasContext.fillStyle = "#000";
canvasContext.font="20px Arial";
canvasContext.fillText("x",40,155);
}

$(canvas).on('mousedown', function (e) {
if(e.which === 1) { 
vm.leftMButtonDown = true;
canvasContext.fillStyle = "#000";
var x = e.pageX - $(e.target).offset().left;
var y = e.pageY - $(e.target).offset().top;
canvasContext.moveTo(x, y);
}
e.preventDefault();
return false;
});

$(canvas).on('mouseup', function (e) {
if(vm.leftMButtonDown && e.which === 1) {
vm.leftMButtonDown = false;
vm.isSign = true;
}
e.preventDefault();
return false;
});

// draw a line from the last point to this one
$(canvas).bind('mousemove', function (e) {
if(vm.leftMButtonDown == true) {
canvasContext.fillStyle = "#000";
var x = e.pageX - $(e.target).offset().left;
var y = e.pageY - $(e.target).offset().top;
canvasContext.lineTo(x,y);
canvasContext.stroke();
}
e.preventDefault();
return false;
});
},
closePopUp(arg = null) {
this.isClose =  !this.isClose;
console.log(this.isClose)
},
changeValue(e){
console.log("select");
},
setData(params){
if(params.pid){
console.log( params.position)
this.register.name = params.name;
this.register.pid = params.pid;
this.register.email = params.email;
this.register.tel = params.tel;
this.register.position = params.position;
this.register.imgData = params.imgData;
this.imgData = params.imgData}

},
onFileChange(e) {
var files = e.target.files || e.dataTransfer.files;
if (!files.length)
return;
this.createImage(files);
},
showSuccess(e){
this.loading = false;
if (e === "OK") {
this.forminner = false;
this.success = true} else {
this.showError(e);
}
},
showError(e) {
alert(e);
this.process = e;
this.redText = true},

restartForm() {
this.forminner = true;
this.success = false//         $('#success')    .hide();
this.progress = "";
this.redText = false},
createImage(files) {

var vm = this;
for (var index = 0; index < files.length; index++) {
var name = files[index].name
var reader = new FileReader();
reader.onload = function(event) {
var imageUrl = event.target.result
vm.register.images.push({name: name, image: imageUrl});
}
reader.readAsDataURL(files[index]);
}



},
submitForm(){
var vm = this;
vm.fun_submit()
vm.loading = true;
if(vm.register.pid){
google.script.run.withSuccessHandler(vm.showSuccess)
.update_value(vm.register.images, vm.register.pid, vm.register.name,
vm.register.email, vm.register.tel,
vm.register.position, vm.imgData);

}else{
google.script.run.withSuccessHandler(vm.showSuccess)
.uploadFileToGoogleDrive(vm.register.images, vm.register.name,
vm.register.email, vm.register.tel,
vm.register.position, vm.imgData);
}

},

}

})
const NotFoundPage = {
name: 'NotFoundPage',
template: '#NotFoundPage'
}
const HomePage = {
name: 'HomePage',
template: '#HomePage',
data(){
return{
list:[],
loading: false,
}
},
mounted(){
this.loading = true
google.script.run.withSuccessHandler(this.getData).read_value()
},
methods:{
onDelete(item, index){
console.log(item, index)
google.script.run.withSuccessHandler((e) => { console.log(e)})
.delete_value(item.pid, item.name,
item.email, item.tel,
item.position);
},
onEdit(ev){
var vm = this;
new Promise( (resolve,reject) => {
item = ev
resolve(ev);
}).then( (val) => {
this.$router.push({ name: 'register', params: ev})
});

},
getData(value){
this.loading = false;
var sheet = JSON.parse(value)
var rows = sheet[0]
for (var r = 1, l = sheet.length; r < l; r++) {

var record  = {};
for (var p in rows) {
record[rows[p]] = sheet[r][p] ;
}
this.list.push(record);
}
}
}
}
const routes = [
{ path: '/', component: HomePage },
{ path: '/register/:id?', name: 'register', component: RegisterPage },
{ path: '*', component: NotFoundPage },
]

const router = new VueRouter({
routes})






new Vue({
router,
data(){
return{
}
},
mounted(){

},

}).$mount("#app")
  </script>

</body>

</html>
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)