For the organisation of a large scout camp, I am currently trying to build a web form where requests for services (security, infrastructure, materials, etc.) can be made. For this I would like the following:
upload (multiple) files to a google drive folder
write the remaining input (text, names, etc.) to a google spreadsheet
If possible, write the url of the file location to that same spreadsheet
So far, I have been utterly unsuccesful in combining the first two functions. Over the course I have tried combining the methods as described in:
Uploading Multiple Files to Google Drive with Google App Script
HTML form file upload to Google Drive and save URL to google sheet
Google Forms: Send data to spreadsheet
Google Forms file upload complete example
https://gist.github.com/xtman/060edd76c27b7fcb343dfb8e08252693
As per request, I am trying to achieve this the following way: Code to make two functions both happen:
frm.submit(function () {
allFiles = document.getElementById('supportingFiles').files;
if (!frm.checkValidity || frm.checkValidity()) {
if (allFiles.length == 0) {
alert('Error: Please choose at least 1 file to upload.');
google.script.run.record_Data(frm);
return false;
} else {
frm.hide();
alert('Step 1');
var subfolderName = document.getElementById('requesterSubject').value;
$.ajax({
url: '',//URL of webhook endpoint for sending a Slack notification
data: {
title: subfolderName + ' is uploading screenshots',
message: ''
}
});
alert('Step 2');
//google.script.run.withSuccesHandler(record_Data(transformedData).transformData('myForm');
google.script.run.withSuccessHandler(afterSubfolderCreated).createSubfolder(subfolderName);
alert('Step 3');
return false;
}
} else {
alert('Invalid form');
return false;
}
});
This is where the error occurs. It is able to run the code as-is, but when I try to also run the recordData function, it doesn't do anything.
The code of the functions is as follows:
Submit the data
function dataTransform(form){
var transformedData = JSON.stringify( $(form).serializeArray() ); // <-----------
console.log( data );
return transformedData; //don't submit
}
function recordData(e) {
Logger.log(JSON.stringify(e)); // log the POST data in case we need to debug it
try {
var doc = SpreadsheetApp.openById('some id');
var sheet = doc.getSheetByName('responses'); // select the responses sheet
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [ new Date() ]; // first element in the row should always be a timestamp
// loop through the header columns
for (var i = 1; i < headers.length; i++) { // start at 1 to avoid Timestamp column
if(headers[i].length > 0) {
row.push(e.parameter[headers[i]]); // add data to row
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
}
catch(error) {
Logger.log(e);
}
finally {
return;
}
}
Submit the files
function uploadFileToDrive(base64Data, fileName, subfolderId) {
Logger.log(subfolderId);
try{
var splitBase = base64Data.split(','),
type = splitBase[0].split(';')[0].replace('data:','');
var byteCharacters = Utilities.base64Decode(splitBase[1]);
var ss = Utilities.newBlob(byteCharacters, type);
ss.setName(fileName);
var subfolder = DriveApp.getFolderById(subfolderId);
var file = subfolder.createFile(ss);
Logger.log(file);
return file.getName() + ' at ' + file.getUrl();
} catch(e) {
return 'createFile Error: ' + e.toString();
}
}
function createSubfolder(subfolderName) {
var dropbox = Utilities.formatDate(new Date(), "Europe/Amsterdam", "yyyy-MM-dd_hh.mm_") + subfolderName ;
Logger.log(dropbox);
var parentFolderId = "some folder id";
var parentFolder = DriveApp.getFolderById(parentFolderId);
var folder;
try {
folder = parentFolder.getFoldersByName(dropbox).next();
}
catch(e) {
folder = parentFolder.createFolder(dropbox);
}
Logger.log(folder);
return folder.getId();
}
function afterSubfolderCreated(subfolderId) {
console.log(subfolderId);
console.log(allFiles);
numUploads.total = allFiles.length;
$('#progressbar').progressbar({ value: false });
$(".progress-label").html('Preparing files for upload');
for (var i = 0; i < allFiles.length; i++) {
console.log(i);
sendFileToDrive(allFiles[i], subfolderId);
}
}
function sendFileToDrive(file, subfolderId) {
var reader = new FileReader();
reader.onload = function (e) {
var content = reader.result;
console.log('Sending ' + file.name);
google.script.run.withSuccessHandler(updateProgressbar).uploadFileToDrive(content, file.name, subfolderId);
}
reader.readAsDataURL(file);
}
function updateProgressbar(idUpdate) {
console.log('Received: ' + idUpdate);
numUploads.done++;
var porc = Math.ceil((numUploads.done / numUploads.total) * 100);
$("#progressbar").progressbar({value: porc});
$(".progress-label").text(numUploads.done + '/' + numUploads.total);
if (numUploads.done == numUploads.total) {
numUploads.done = 0;
$(".progress-label").text($(".progress-label").text() + ': FINISHED!');
$("#progressbar").after('(Optional) Refresh this page if you want to fill out another request.');
//<a href="javascript:window.top.location.href=window.top.location.href"> does not work
}
}
function fileUploaded(status) {
document.getElementById('myForm').style.display = 'none';
document.getElementById('output').innerHTML = status;
}
