javascript html css google-sheets digital-signature

Signature Pad (Canvas) to Google Sheets

I'm attempting to create a form which auto populates into Google Sheets. At the moment, I am able to use <input/> to auto populate basic text fields into Google Sheets, but I now need to try to have the user write a digital signature and for that to be put into Google Sheets too - presumably by using toDataURL and then having that be sent to the Google Sheet?

But... I have very little experience with anything like this and have absolutely no idea how to achieve this!

I have taken code from various sources and videos so far to get to the stage I'm at, but now it is getting messy.

Some of the content on the page is somewhat sensitive, so I'll post whatever code I can here.

This is some of the HTML currently being used - sorry that it is a little messy! (Some of it might not even have use, I've been messing around with it so much that I'm lost at this point!)

    <h4>Billing Address</h4>
        <input type="text" name="address-number" placeholder="House/Apartment Number">
            <input type="text" name="address-street" placeholder="Street Name">
        <input type="text" name="address-town" placeholder="Town/City">
        <input type="text" name="address-county" placeholder="County">
            <input type="text" name="address-postcode" placeholder="Postcode">
                    
                    
    <div class="flex-row">
                <div class="wrapper">
                     <canvas id="signature-pad" width="400" height="200"></canvas>
                </div>
                <div>
                     <input type="hidden" name="signature-pad" />
                </div>
                     <div class="clear-btn">
                     <button id="clear"><span> Clear </span></button>
                </div>
                </div> 
                
                 <div>
                
<script src="https://cdnjs.cloudflare.com/ajax/libs/signature_pad/1.3.5/signature_pad.min.js" integrity="sha512-kw/nRM/BMR2XGArXnOoxKOO5VBHLdITAW00aG8qK4zBzcLVZ4nzg7/oYCaoiwc8U9zrnsO9UHqpyljJ8+iqYiQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>

   <input type="submit" value="Submit" id="submit">
</div>
</form>

And this is the Javascript currently being used - it's even more messy and I'm sure a lot of it is doing nothing. Apologies once again.

const form = document.forms['contact-form']


form.addEventListener('submit', e => {
  e.preventDefault()
  fetch(scriptURL, { method: 'POST', body: new FormData(form)})
  .then(response => alert("Thank you! your form is submitted successfully." ))
  .then(() => { window.location.reload(); })
  .catch(error => console.error('Error!', error.message))
})

 
var canvas = document.getElementById("signature-pad");


       function resizeCanvas() {
           var ratio = Math.max(window.devicePixelRatio || 1, 1);
           canvas.width = canvas.offsetWidth * ratio;
           canvas.height = canvas.offsetHeight * ratio;
           canvas.getContext("2d").scale(ratio, ratio);
       }
       
       window.onresize = resizeCanvas;
       resizeCanvas();

       var signaturePad = new SignaturePad(canvas, {
        backgroundColor: 'rgb(250,250,250)'
       });

       document.getElementById("clear").addEventListener('click', function(){
        signaturePad.clear();
       });
       
       
function onSubmit(e) {
    console.log({
        'signature-pad' : dataURL,
    });
}

And this is all connected up to Google Sheets with some kind of script - I honestly have no idea how that is working at all. It's all a bit dark magic to me. If I need to provide any more code or info then let me know.

Fundamentally, I'd like to be able to get the signature pad to out put the signature image in some way which I can then have auto populate into Google Sheets when the user submits.

Thanks so much for any help with this and to anyone who can decipher my descent into madness. It is VERY much appreciated!

I've tried many different variations of code, most of which I have since lost. Apologies. Most of the time, I would half-expect it to work (with little confidence) and it simply would not. The Google Sheet would auto populate as usual, but without any signature section filled. I have attempted to alter the onSubmit section to generate a URL, though I don't think I did it correctly. And I have tried things such as having other "var" segments at the beginning of the code such as with this example: Create an HTML form with Digital / Electronic Signature using php - but I've found that those "var"s at the beginning seem to break the signature pad in Wordpress and it will not allow a user to even draw in the box. As soon as I remove them, the signature pad works again.

I've tried using both the code I currently have, and the one linked but neither work. The one I currently have allows me to use the pad properly but I cannot work out how to generate the URL and send it to Google Sheets as the other code might allow. However the linked code will not allow the user to use the signature pad. I am unsure as to why and I have tried merging the code in different places and taking bits from each, but to no success, everytime I fail due to it not being able to be drawn in, or not knowing how to send to URL to Google Sheets.

Thank you for any help!

Solution

Alternative Solution: Use Google App Script to Get the Signature

I created a script that automatically sends all of the details including the signature to Google Sheet. Kindly follow the step by step procedure below.

Steps:

Create a folder on your Google Drive and make the folder Anyone with the link and select Viewer.
Create a Spreadsheet where you will store the data and input the headers of each value.
Open AppScript via Extensions at the menu bar of the Spreadsheet.
Paste this code on Code.gs:

function doGet() {
  // Creates and returns an HTML output from the 'form' file, setting the title of the window.
  return HtmlService.createHtmlOutputFromFile('form')
    .setTitle('Form with Signature');
}

function doPost(e) {
  // Extracts form data from the event object (e).
  const formData = e.parameter;

  // Open the Google Sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet(); // Gets the active spreadsheet.
  const sheetName = sheet.getSheetByName("Sheet1"); // Gets the specific sheet named "Sheet1".

  // Retrieve the signature from the form data
  const signature = formData.signature;

  let fileUrl = ''; // Variable to store the URL of the uploaded signature file.
  let blob = null;  // Variable to hold the blob of the image.
  let id = null;    // Variable to hold the ID of the uploaded file.

  if (signature) {
    // If there is a signature, process it
    // Remove the prefix "data:image/png;base64," to get the base64 string.
    const base64Image = signature.split(',')[1];

    // Convert the base64 string to a Blob object (binary large object).
    blob = Utilities.newBlob(Utilities.base64Decode(base64Image), 'image/png', 'signature.png');

    // Save the Blob to Google Drive in a specific folder (use your folder ID).
    const folder = DriveApp.getFolderById('REPLACE-WITH-YOUR-ACTUAL-FOLDER-ID'); // Replace with your folder ID.
    const file = folder.createFile(blob); // Creates the file in the folder.
    fileUrl = file.getUrl(); // Gets the URL of the newly created file.
    
    // Extract the file ID from the URL
    id = fileUrl.split("/");
    
    // Create an IMAGE formula for Google Sheets to display the uploaded image.
    formData.image = `=IMAGE("https://drive.google.com/thumbnail?sz=w1000&id=${id[5]}", 4, 50, 100)`; 
  }

  // Add the form data to the Google Sheet
  sheetName.appendRow([
    formData['address-number'],  // Adds address number from form data.
    formData['address-street'],  // Adds address street from form data.
    formData['address-town'],     // Adds address town from form data.
    formData['address-county'],   // Adds address county from form data.
    formData['address-postcode'],  // Adds address postcode from form data.
    formData.image                // Adds the image formula for the signature.
  ]);

  // Returns a success message as plain text.
  return ContentService.createTextOutput("Success");
}

NOTE: On line 31 of your code, make sure to paste your actual Folder ID.

Create an HTML file by clicking the + symbol.

Paste this code on the newly created HTML file:

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet"
    integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">

  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"
    integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous">
  </script>
  <style>
    #signature-pad {
      border: 1px solid #000;
      width: 400px;
      height: 200px;
    }

    .clear-btn {
      margin-top: 10px;
    }
  </style>
</head>

<body>
  <h4>Billing Address</h4>
  <form id="contact-form">
    <input type="text" name="address-number" placeholder="House/Apartment Number" required><br>
    <input type="text" name="address-street" placeholder="Street Name" required><br>
    <input type="text" name="address-town" placeholder="Town/City" required><br>
    <input type="text" name="address-county" placeholder="County" required><br>
    <input type="text" name="address-postcode" placeholder="Postcode" required><br>

    <h4>Signature</h4>
    <canvas id="signature-pad"></canvas>
    <input type="hidden" name="signature" id="signature">
    <div class="clear-btn">
      <button type="button" id="clear">Clear</button>
    </div>

    <br>
    <input type="submit" value="Submit">
  </form>

  <script src="https://cdnjs.cloudflare.com/ajax/libs/signature_pad/1.3.5/signature_pad.min.js"></script>
  <script>
    // Signature Pad setup
      var canvas = document.getElementById("signature-pad");
      var signaturePad = new SignaturePad(canvas, {
        backgroundColor: 'rgb(250, 250, 250)'
      });

      // Clear the signature
      document.getElementById("clear").addEventListener('click', function() {
        signaturePad.clear();
      });

      // Submit form with signature
      var form = document.getElementById('contact-form');
      form.addEventListener('submit', function(e) {
        e.preventDefault();

        // Check if signature is empty
        if (signaturePad.isEmpty()) {
          alert("Please provide a signature first.");
          return;
        }

        // Convert signature to data URL
        var signatureDataURL = signaturePad.toDataURL();

        // Set the value of hidden input field with the signature data
        document.getElementById("signature").value = signatureDataURL;

        // Send form data to Apps Script
        fetch("https://script.google.com/macros/s/AKfycbz0MurKGjNAsWdgpi2ifjSfHj-gQQuBFpSlGVq0hg5xHn7lP_vZosUxzzMrGA4b3GfdtA/exec", { //Replace this with your actual Web App Link
          method: 'POST',
          body: new FormData(form)
        })
        .then(response => response.text())
        .then(result => {
          alert("Form submitted successfully!");
          form.reset();  // Reset the form after submission
          signaturePad.clear();  // Clear the signature pad
        })
        .catch(error => {
          console.error('Error:', error);
        });
      });
  </script>
</body>

</html>

Deploy your Web App by clicking the Deploy on the upper right of your screen. Click New Deployment select Web App as type and fill out the necessary form. Make sure to choose Anyone on who has access.

Copy the URL of the Web App that will appear after you deploy because you will be pasting it on the HTML file and you will be needing it to access your WebApp.

NOTE: On line 76 of your code, make sure to paste your actual Web App URL.

Sample Output:

WebApp:

Spreadsheet

References:

Please note that I am not affiliated with the website provided. I just happened to find this site and it works perfectly.