Search code examples
htmlgoogle-apps-scriptgoogle-sheets-apijsignature

JSignature and other HTML form fields to Googlesheets

I am trying to pass a JSignature field and other form fields to a google sheet. I can get the signature to save to the drive and inserted into the googlesheet as an image. But the fields 'name' and 'email' dont come through to the sheet. I think it something to do with

google.script.run.writeForm(this.parentNode)

This is my sign.html

<!DOCTYPE html>
<!-- from https://stackoverflow.com/questions/43054790/saving-image-to-spreadsheet-with-google-scripts/43058633#43058633-->
<html>
  <head></head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="https://cdn.rawgit.com/willowsystems/jSignature/master/libs/jSignature.min.js"></script>
<body>
<form id="customerForm">

Please sign your name in the pad below: <br>

First Name: <input type="text" name="username"><br>
Email: <input type="email" name="useremail"><br>
<div id="signature"></div><br>
<img id="rendered" src=""style="display:none">
<input type="button" value="Save" onclick="renderSignature();saveImage();google.script.run.writeForm(this.parentNode)"/>

<!--<input type="button" value="Add to Sheet" onclick="saveImage()"/>
<input type="button" value="Close" onclick="google.script.host.close()" />-->
</form>
</body>

<script>

  $("#signature").jSignature({
    'background-color': 'transparent',
    'decor-color': 'transparent'
  });

  function renderSignature(){
    $("img#rendered").attr("src",$('#signature').jSignature('getData','default'));
  }

  function saveImage(){ //This sends the image src to saveImages function
  var bytes = document.getElementById('rendered').src
  console.log(bytes)
  google.script.run.saveImage(bytes)
  } 

</script>
</html>

At this is my code.gs

function showDialog() {
  var html = HtmlService.createHtmlOutputFromFile('sign')
    .setWidth(400)
    .setHeight(300);
   SpreadsheetApp.getUi()
  .showModalDialog(html, 'Your Signature is Required');


}

function doGet() {
  return HtmlService
      .createTemplateFromFile('sign')
      .evaluate();
}


function saveImage(bytes){
  var dateObj = Date.now();
  var bytes = bytes.split(",")
  var blob = Utilities.newBlob(Utilities.base64Decode(bytes[1]), 'image/png');
  var fileName = blob.setName("Signature "+dateObj).getName();
  var sigFolder = DriveApp.getFolderById("12ymURZrnC5Y4XYxYxCEpxJCHYU7V4Oz9")
  var url = sigFolder.createFile(blob).getId();



/*function insertImage(){
  var sigFolder = DriveApp.getFolderById("1p8Wt9SGJcbuwOw3UDz9RJA4pLDno7RCk")
  var fileURL = sigFolder.getId()*/
  Logger.log(url)

function writeForm (){

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Register')
  var name = sign.username;
  var email = sign.useremail;
  var signature = '=image("https://drive.google.com/uc?export=view&id='+url+'")'
  var imageCell = ss.getRange(ss.getLastRow()+1, 1, 1, 4).setValues([[Date(), name,email,signature]]);
  }

}
Solution

  • In your scripts, I thought that you might have feel a issue for url in GAS. So how about this modification? I think that there are some solutions for your situation. So please think of this as one of them.

    Modification points :

    • Merge writeForm() to saveImage() of Javascript.
    • Retrieve username and useremail at saveImage(), and send them to saveImage() of GAS.

    Modified scripts :

    HTML : 
    <!DOCTYPE html>
<html>
<head></head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="https://cdn.rawgit.com/willowsystems/jSignature/master/libs/jSignature.min.js"></script>
<body>
<form id="customerForm">
Please sign your name in the pad below: <br>
First Name: <input type="text" name="username"><br>
Email: <input type="email" name="useremail"><br>
<div id="signature"></div><br>
<img id="rendered" src="" style="display:none">
<input type="button" value="Save" onclick="renderSignature();saveImage()"/>
</form>
</body>
<script>
  $("#signature").jSignature({
    'background-color': 'transparent',
    'decor-color': 'transparent'
  });

  function renderSignature(){
    $("img#rendered").attr("src",$('#signature').jSignature('getData','default'));
  }

  function saveImage(e){ //This sends the image src to saveImages function
    var bytes = document.getElementById('rendered').src;
    console.log(bytes);
    var sign = {
      username: document.getElementsByName('username')[0].value,
      useremail: document.getElementsByName('useremail')[0].value
    };
    google.script.run.saveImage(bytes, sign);
  } 
</script>
</html>
    GAS : 
    function saveImage(bytes, sign){
  var dateObj = Date.now();
  var bytes = bytes.split(",")
  var blob = Utilities.newBlob(Utilities.base64Decode(bytes[1]), 'image/png');
  var fileName = blob.setName("Signature "+dateObj).getName();
  var sigFolder = DriveApp.getFolderById("12ymURZrnC5Y4XYxYxCEpxJCHYU7V4Oz9");
  var url = sigFolder.createFile(blob).getId();
  Logger.log(url)
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Register');
  var name = sign.username;
  var email = sign.useremail;
  var signature = '=image("https://drive.google.com/uc?export=view&id='+url+'")'
  var imageCell = ss.getRange(ss.getLastRow()+1, 1, 1, 4).setValues([[Date(), name,email,signature]]);
}

    If this was not what you want to do, I'm sorry.