Upload an image to a Google spreadsheet from WEB FORM

Question

So basically Its automatically generated script from google sheets. I can't find a link or anything which can help me to tweak it so i could upload an image from my website. It sends only the image name instead of an actual image. I would be very glad if someone could explain me how to tweak it so it would send the actual image.

<input type="text" id="email" name="email:" placeholder="">
 <input type="file" id="myFile" name="filename">
                    
 <button>
   Send
</button>

Out put
Email: Someone@gmail.com
FileName: Untitled-1.jpg

// if you want to store your email server-side (hidden), uncomment the next line
 var TO_ADDRESS = "***@gmail.com";

// spit out all the keys/values from the form in HTML for email
// uses an array of keys if provided or the object to determine field order
function formatMailBody(obj, order) {
  var result = "";
  if (!order) {
    order = Object.keys(obj);
  }
  
 
  
  // loop over all keys in the ordered form data
  for (var idx in order) {
    var key = order[idx];
    result += "<h4 style='text-transform: capitalize; margin-bottom: 0'>" + key + "</h4><div>" + sanitizeInput(obj[key]) + "</div>";
    // for every key, concatenate an `<h4 />`/`<div />` pairing of the key name and its value, 
    // and append it to the `result` string created at the start.
  }
  return result; // once the looping is done, `result` will be one long string to put in the email body
}

// sanitize content from the user - trust no one 
// ref: https://developers.google.com/apps-script/reference/html/html-output#appendUntrusted(String)
function sanitizeInput(rawInput) {
   var placeholder = HtmlService.createHtmlOutput(" ");
   placeholder.appendUntrusted(rawInput);
  
   return placeholder.getContent();
 }

function doPost(e) {

  try {
    Logger.log(e); // the Google Script version of console.log see: Class Logger
    record_data(e);
    
    // shorter name for form data
    var mailData = e.parameters;

    // names and order of form elements (if set)
    var orderParameter = e.parameters.formDataNameOrder;
    var dataOrder;
    if (orderParameter) {
      dataOrder = JSON.parse(orderParameter);
    }
    
    // determine recepient of the email
    // if you have your email uncommented above, it uses that `TO_ADDRESS`
    // otherwise, it defaults to the email provided by the form's data attribute
    var sendEmailTo = (typeof TO_ADDRESS !== "undefined") ? TO_ADDRESS : mailData.formGoogleSendEmail;
    
    // send email if to address is set
    if (sendEmailTo) {
      MailApp.sendEmail({
        to: String(sendEmailTo),
        subject: "Contact form submitted",
        // replyTo: String(mailData.email), // This is optional and reliant on your form actually collecting a field named `email`
        htmlBody: formatMailBody(mailData, dataOrder)
      });
    }

    return ContentService    // return json success results
          .createTextOutput(
            JSON.stringify({"result":"success",
                            "data": JSON.stringify(e.parameters) }))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(error) { // if error return this
    Logger.log(error);
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": error}))
          .setMimeType(ContentService.MimeType.JSON);
  }
}


/**
 * record_data inserts the data received from the html form submission
 * e is the data received from the POST
 */
function record_data(e) {
  var lock = LockService.getDocumentLock();
  lock.waitLock(30000); // hold off up to 30 sec to avoid concurrent writing
  
  try {
    Logger.log(JSON.stringify(e)); // log the POST data in case we need to debug it
    
    // select the 'responses' sheet by default
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheetName = e.parameters.formGoogleSheetName || "responses";
    var sheet = doc.getSheetByName(sheetName);
    
    var oldHeader = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var newHeader = oldHeader.slice();
    var fieldsFromForm = getDataColumns(e.parameters);
    var row = [new Date()]; // first element in the row should always be a timestamp
    
    // loop through the header columns
    for (var i = 1; i < oldHeader.length; i++) { // start at 1 to avoid Timestamp column
      var field = oldHeader[i];
      var output = getFieldFromData(field, e.parameters);
      row.push(output);
      
      // mark as stored by removing from form fields
      var formIndex = fieldsFromForm.indexOf(field);
      if (formIndex > -1) {
        fieldsFromForm.splice(formIndex, 1);
      }
    }
    
    // set any new fields in our form
    for (var i = 0; i < fieldsFromForm.length; i++) {
      var field = fieldsFromForm[i];
      var output = getFieldFromData(field, e.parameters);
      row.push(output);
      newHeader.push(field);
    }
    
    // more efficient to set values as [][] array than individually
    var nextRow = sheet.getLastRow() + 1; // get next row
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

    // update header row with any new data
    if (newHeader.length > oldHeader.length) {
      sheet.getRange(1, 1, 1, newHeader.length).setValues([newHeader]);
    }
  }
  catch(error) {
    Logger.log(error);
  }
  finally {
    lock.releaseLock();
    return;
  }

}

function getDataColumns(data) {
  return Object.keys(data).filter(function(column) {
    return !(column === 'formDataNameOrder' || column === 'formGoogleSheetName' || column === 'formGoogleSendEmail' || column === 'honeypot');
  });
}

function getFieldFromData(field, data) {
  var values = data[field] || '';
  var output = values.join ? values.join(', ') : values;
  return output;
}```

Answer 1

In Apps Script web apps, in order to send file-input data from the HTML form to the server, the recommended way is to call a server-side function via google.script.run. In this case, you wouldn't rely on a doPost function (called when POST requests are made to the web app URL), but on a server-side function (named processForm in the sample below) called via google.script.run.

When calling a server-side function this way, if you pass the HTML form element as a parameter, you can access the input field names, as explained here:

If you call a server function with a form element as a parameter, the form becomes a single object with field names as keys and field values as values. The values are all converted to strings, except for the contents of file-input fields, which become Blob objects.

Now, you have several options when deciding how to insert the image to the spreadsheet, depending on:

• Whether you want to insert the image to a specific cell or insert the image over cells (not bounded to a specific cell).
• The size of the image.
• Whether you are concerned about making this image publicly accessible.

Method #1. Use insertImage:

In case you want to insert the image "over cells" (that is, the image is not located in one specific cell), and in case you don't want this image to be publicly accessible, you can just provide the Blob corresponding to the image as a parameter for Sheet.insertImage(blobSource, column, row).

The corresponding code could be something like this:

index.html:

<html>
  <body>
    <form>
      <input type="text" id="email" name="email:" placeholder="">
      <input type="file" id="myFile" name="filename">      
      <input type="button" value="Send" onclick="google.script.run.processForm(this.parentNode)">
    </form>
  </body>
</html>

Code.gs:

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index');
}

function processForm(e) {
  var contentType = 'image/jpeg'; // Could also be 'image/bmp', 'image/gif', or 'image/png'
  var image = e.filename;
  var image = image.getAs(contentType);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1"); // Change sheet name if necessary
  sheet.insertImage(image, 1, 1); // Top-left corner of image is top-left corner of A1 (image not in A1, but "over cells")
}

Other data coming from input fields can be retrieved from e using the input name. For example, the inputted email would be retrieved via e["email:"].

Important: Because of this issue, images above a certain size cannot be inserted to a sheet via insertImage; trying to do so results in the following error message Exception: Service error: Spreadsheets (I have noticed this works with an image around 150 KB, but not with one around 260 KB). So if you want to insert an image with a higher size, you might want to check method #2 (or resize the image before inserting, for example, using the workaround found in this answer by Tanaike).

Method #2. Use =IMAGE(url):

If you want to insert the image to a specific cell, and you are not concerned with this image being publicly accessible, you could insert the image by creating a publicly accessible link to this image and using the function IMAGE.

For example, you could upload this image to Drive, make it publicly accessible (shared with Anyone with the link), and add a public URL via =IMAGE(url). In this case, your server-side function could be something like this (see this for more details on the URL):

function processForm(e) {
  var contentType = 'image/jpeg';
  var image = e.filename;
  var image = image.getAs(contentType);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1"); // Change sheet name if necessary
  var file = DriveApp.createFile(image); // Add image to Drive
  file.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW); // Share image with Anyone
  sheet.getRange("A1").setFormula("=IMAGE(\"https://drive.google.com/uc?export=view&id=" + file.getId() + "\")");
}

An advantage of this method is that you can insert images with a higher size.

Important note:

• Currently, images cannot be successfully uploaded via HTML form file-input fields if V8 is enabled due to this issue. Because of this, you have to disable the V8 runtime for this to work (click Run > Disable new Apps Script runtime powered by Chrome V8).