-2

I want to build contact data using App Scripts and Google Sheets as here

enter image description here

Full App Script code is here

I just need to know how can I add the contact PHOTO, so it is getting uploaded in the form, and saved in the Google Sheets (or at Google Drive with link in the Google Sheets)

I read this but it is different than what I want.

Hasan A Yousef
  • 22,789
  • 24
  • 132
  • 203

1 Answers1

2

I believe your goal as follows.

  • You want to add the function for uploading an image file to your current Google Apps Script project.

Modification points:

  • In this case,
    • Please add a tag of <input type="file"> to Form.html.
    • In the current stage, when V8 runtime is used, in the case of the binary data, the form object cannot be correctly parsed at Google Apps Script side. Ref So, the binary data (image file) is sent to Google Apps Script side as the byte array.
    • By this, it is required to modify the function of processForm in your Google Apps Script.

When above points are reflected to your script, it becomes as follows.

Modified script:

HTML side: Form.html

Please modify Form.html as follows.

From: 
<button type="submit" class="btn btn-primary">Submit</button>
<input class="btn btn-secondary" type="reset" value="Reset">
To: 
<div><input type="file" id="file" name="file" accept="image/png,image/jpeg"></div>  <!-- Added -->

<button type="submit" class="btn btn-primary">Submit</button>
<input class="btn btn-secondary" type="reset" value="Reset">

Javascript side: JavaScript.html

Please modify JavaScript.html as follows.

From: 
function handleFormSubmit(formObject) {
  google.script.run.withSuccessHandler(createTable).processForm(formObject);
  document.getElementById("myForm").reset();
}
To: 
// I added below function. This is from https://gist.github.com/tanaikech/58d96c023468fc1922d67764251b25e0
const parseValues = async (e) =>
  Object.assign(
    ...(await Promise.all(
      [...e].map(
        (obj) =>
          new Promise(async (res) => {
            const temp = {[obj.name]: ""};
            if (obj.type == "radio") {
              if (obj.checked === true) {
                temp[obj.name] = obj.value;
              }
            } else if (obj.type == "file") {
              const files = obj.files;
              temp[obj.name] = await (async (files) => {
                return await Promise.all(
                  [...files].map(
                    (file) =>
                      new Promise((resolve, reject) => {
                        const fr = new FileReader();
                        fr.onload = (f) =>
                          resolve({
                            filename: file.name,
                            mimeType: file.type,
                            bytes: [...new Int8Array(f.target.result)],
                          });
                        fr.onerror = (err) => reject(err);
                        fr.readAsArrayBuffer(file);
                      })
                  )
                );
              })(files).catch((err) => console.log(err));
            } else {
              temp[obj.name] = obj.value;
            }
            res(temp);
          })
      )
    ))
  );

async function handleFormSubmit(formObject) {  // Modified
  var obj = await parseValues(formObject);  // Added
  google.script.run.withSuccessHandler(createTable).processForm(obj);  // Modified
  document.getElementById("myForm").reset();
}

Google Apps Script side: Code.gs

From: 
function processForm(formObject){  
  if(formObject.RecId && checkID(formObject.RecId)){//Execute if form passes an ID and if is an existing ID
    updateData(getFormValues(formObject),globalVariables().spreadsheetId,getRangeByID(formObject.RecId)); // Update Data
  }else{ //Execute if form does not pass an ID
    appendData(getFormValues(formObject),globalVariables().spreadsheetId,globalVariables().insertRange); //Append Form Data
  }
  return getLastTenRows();//Return last 10 rows
}
To: 
// I added below function.
function saveFileToDrive(e) {
  var blob = Utilities.newBlob(e.bytes, e.mimeType, e.filename);
  var file = DriveApp.getFolderById("root").createFile(blob);  // In this case, the image is saved to the root folder. Please modify `root` to your actulal folder ID.
  return file.getDownloadUrl();
}

function processForm(formObject){

  var fileLink = formObject.file.length > 0 ? saveFileToDrive(formObject.file[0]) : "";  // Added

  if(formObject.RecId && checkID(formObject.RecId)){//Execute if form passes an ID and if is an existing ID
    updateData(getFormValues(formObject),globalVariables().spreadsheetId,getRangeByID(formObject.RecId)); // Update Data
  }else{ //Execute if form does not pass an ID
    appendData(getFormValues(formObject),globalVariables().spreadsheetId,globalVariables().insertRange); //Append Form Data
  }
  return getLastTenRows();//Return last 10 rows
}
  • When above modification is reflected to your Google Apps Script project, when you opened the Web Apps, you can see the file input tag. When you selected an image file and click the button, the form object is parsed by a function of parseValues(), and the parsed object is sent to Google Apps Script side. At Google Apps Script side, when the image file is uploaded, the image data is saved as a file to Google Drive and the download link is returned as fileLink.
  • About the value of fileLink, please use it for your actual situation by modifying the script.

Note:

  • From your script, it seems that you are using Web Apps. So, when you modified the script, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.
  • This is a simple modification. So please modify the style of HTML and the values for putting to Google Spreadsheet for your actual situation.
  • In your request in your question, you say I just need to know how can I add the contact PHOTO, so it is getting uploaded in the form, and saved in the Google Sheets (or at Google Drive with link in the Google Sheets).
    • About this, when the image is put to the Spreadsheet as a blob, it is a bit complecate for retrieving the image from Spreadsheet again. So in my answer, I proposed to retrieve the file link by saving the image data as a file on Google Drive.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165