Importing Google Apps Script form submission into existing Spreadsheet

Question

I have a Google Drive with a folder called "Submissions". I also have a Google Apps script which both creates a 'form' as well as depositing what the end-user has uploaded (from the form) into the submissions folder.

Here is an example of the script (server.gs):

 function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('form.html');
}

function uploadFiles(form) {

  try {

    var dropbox = "Submissions";
    var folder, folders = DriveApp.getFoldersByName(dropbox);

    if (folders.hasNext()) {
      folder = folders.next();
    } else {
      folder = DriveApp.createFolder(submissions);
    }

    var blob = form.myFile;    
    var file = folder.createFile(blob);    
    file.setDescription("Uploaded by " + form.myName);

    return "File uploaded successfully " + file.getUrl();

  } catch (error) {

    return error.toString();
  }

}

And the Form.html is as follows:

<form id="myForm">

<label>Name:</label>
<input type="text" name="myName">

<label>Email:</label>
<input type="text" name="myEmail">

<label>Demo Title (e.g "Artist - Demo Name"):</label>
<input type="text" name="myTitle">

<label>Choose Demo File:</label>
<input type="file" name="myFile">

<input type="submit" value="Submit" 
           onclick="google.script.run
                    .uploadFiles(this.parentNode);
                    return false;">

What this does is create a form which has "Name", "Email", "Demo Title" text fields as well as a "Submit" button. The user attaches a file and uploads the file using this form.

Now the file upload works absolutely fine. Anyone can upload a file and it will appear in a folder (Submissions) on my google drive correctly... However, the other details simply vanish as if they were never entered. I want THOSE details to appear in an existing spread sheet with preferably a timestamp.

Can anyone help me?

Answer 1

You will have to use Class SpreadsheetApp, with this class you will be able to use SpreadsheetApp.getActiveSpreadsheet(), getSheetByName(), getRange(), getLastRow(), and getLastColumn() and more.

Here is a sample code from this tutorial:

var doc = SpreadsheetApp.openById(ID);
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 = e.parameter.header_row || 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 { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
// 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);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}

EDIT:

For example this is your code

 <form id="myForm">

<label>Name:</label>
<input type="text" name="myName">

<label>Email:</label>
<input type="text" name="myEmail">

<label>Demo Title (e.g "Artist - Demo Name"):</label>
<input type="text" name="myTitle">

<label>Choose Demo File:</label>
<input type="file" name="myFile">

<input type="submit" value="Submit" 
           onclick="google.script.run
                    .uploadFiles(this.parentNode);
                    return false;">

(server.gs)

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('form.html');
}

function uploadFiles(form) {

  try {

    var dropbox = "Submissions";
    var folder, folders = DriveApp.getFoldersByName(dropbox);

    if (folders.hasNext()) {
      folder = folders.next();
    } else {
      folder = DriveApp.createFolder(submissions);
    }

    var blob = form.myFile;    
    var file = folder.createFile(blob);    
    file.setDescription("Uploaded by " + form.myName);

    return "File uploaded successfully " + file.getUrl();

    //pass data to spreadsheet
    processForm(YOUR_VARIABLES)

  } catch (error) {

    return error.toString();
  }

}
function processForm(YOUR_VARIABLES) {

  var Name = name;
  var email = email;


  var ss = SpreadsheetApp.openById(SPEADSHEET_ID); 
  var sheet = ss.getSheetByName(SHEET_NAME);

  //Change this depending on the order and number of inputs to be recorded
  sheet.getRange(sheet.getLastRow()+1, 1, 1, 5).setValues([[Name,email]]);  
}

You can also check this answers to get some ideas on how to implement the spreadsheet app : Get form input text value to insert in a Google Spreadsheet , Uploading file using Google Apps Script using HtmlService