Upload an image to a Google spreadsheet

Question

I am making a photo shooting contest, the competitor should register using a Google registration form, and upload his photo as well. I searched all over the internet to find a Google script that can be inserted into a form to upload a file using Google forms but could not find anything. Is it doable and how, and even if there can be other ideas to do such thing please let me know.

Solution 1

Try this

function insertImage() {
  // Retrieve an image from the web.
  var resp = UrlFetchApp.fetch("http://www.google.com/intl/en_com/images/srpr/logo2w.png");

  // Create a document.
  var doc = DocumentApp.openById("");

  // Append the image to the first paragraph.
  doc.getChild(0).asParagraph().appendInlineImage(resp);
}

This link might help you as well :)

http://code.google.com/googleapps/appsscript/class_documentapp_listitem.html#appendInlineImage

Happy coding !

Solution 2

Here is a possible workflow suggestion that includes a form, a spreadsheet with responses and a doc with included images.

the form is testable here

the spreadsheet is viewable here

the doc is viewable here

See EDIT 2

NOTES :

1. for now it works only with small images, I have to find a solution for that. (see EDIT)
2. the image insertion in spreadsheet doesn't work, I commented this line for now...

And here is the full code, still a draft but I think it could be fully implemented if we find a solution to the aforementioned problems.

EDIT : The size of the image is actually not relevant, I had success with images 4 times larger than the page size but in PNG format - it seems that .png is far more reliable in this context, that's after all good news! btw, I can use indifferently the blob, the image file or the so-called thumbnail (which has the very same size as the original ;-) and I get always the same result. I guess I'll have to post an question on that in another post :-D=

var submissionSSKey = '0AnqSFd3iikE3dGFsUWNpb08zVWx5YjFRckloZ0NFZGc';
var docurl = 'https://docs.google.com/document/d/1E6yoROb52QjICsEbGVXIBdz8KhdFU_5gimWlJUbu8DI/'
var listitems = ['Select a category','Portrait','Landscape','Nude','Night shots','Nature','Various']
var Panelstyle = {'background':'#dddddd','padding':'40px','borderStyle':'ridge','borderWidth':'15PX','borderColor':'#aaaaaa'}

function doGet() {
  var app = UiApp.createApplication().setTitle('Photography contest').setStyleAttribute('padding','50PX');
  var panel = app.createFormPanel().setStyleAttributes(Panelstyle).setPixelSize(400, 200);
  var title = app.createHTML('<B>Photography contest</B>').setStyleAttribute('color','grey').setStyleAttribute('fontSize','25PX');
  var grid = app.createGrid(6,2).setId('grid');
  var list1 = app.createListBox().setName('list1');
   for(var i in listitems){list1.addItem(listitems[i])}    
  var Textbox1 = app.createTextBox().setWidth('150px').setName('TB1');
  var email = app.createTextBox().setWidth('150px').setName('mail');
  var upLoad = app.createFileUpload().setName('uploadedFile');
  var submitButton = app.createSubmitButton('<B>Submit</B>'); 
  var warning = app.createHTML('Please fill in all fields').setStyleAttribute('background','#bbbbbb').setStyleAttribute('fontSize','20px');
  //file upload
  var cliHandler2 = app.createClientHandler()
  .validateLength(Textbox1, 1, 40).validateNotMatches(list1,'Select a category').validateEmail(email).validateNotMatches(upLoad, 'FileUpload')
  .forTargets(submitButton).setEnabled(true)
  .forTargets(warning).setHTML('Now you can submit your form').setStyleAttribute('background','#99FF99').setStyleAttribute('fontSize','12px');

  //Grid layout of items on form
  grid.setWidget(0, 1, title)
      .setText(1, 0, 'Category')
      .setWidget(1, 1, list1.addClickHandler(cliHandler2))
      .setText(2, 0, 'Name')
      .setWidget(2, 1, Textbox1.addClickHandler(cliHandler2))
      .setText(3, 0, 'Email')
      .setWidget(3, 1, email)
      .setText(4, 0, 'File Upload')
      .setWidget(4, 1, upLoad.addChangeHandler(cliHandler2))
      .setWidget(5, 0, submitButton)
      .setWidget(5, 1, warning);

  var cliHandler = app.createClientHandler().forTargets(warning).setHTML('<B>PLEASE WAIT WHILE THE FILE IS UPLOADING<B>').setStyleAttribute('background','yellow');
  submitButton.addClickHandler(cliHandler).setEnabled(false);  
  panel.add(grid);
  app.add(panel);
  return app;
}


function doPost(e) {
  var app = UiApp.getActiveApplication();
  var ListVal = e.parameter.list1;
  var textVal = e.parameter.TB1;
  var Email = e.parameter.mail;
  var fileBlob = e.parameter.uploadedFile;
  var img = DocsList.createFile(fileBlob);
  try{
  var folder = DocsList.getFolder('photos');
  }catch(e){DocsList.createFolder('photos');var folder = DocsList.getFolder('photos')}
  img.addToFolder(folder);
  img.removeFromFolder(DocsList.getRootFolder())
  var sheet = SpreadsheetApp.openById(submissionSSKey).getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow();
//  var image = sheet.insertImage(img.getUrl(), 4, lastRow+1)
  var targetRange = sheet.getRange(lastRow+1, 1, 1, 4).setValues([[ListVal,textVal,Email,img.getUrl()]]);
  var GDoc = DocumentApp.openByUrl(docurl)
  GDoc.appendTable([['Category : '+ListVal,'Name : '+textVal,'Email : '+Email]])
  var par = GDoc.appendParagraph('IMAGE PREVIEW')
  par.insertInlineImage(1, img.getThumbnail())
  GDoc.appendHorizontalRule();
  GDoc.saveAndClose();
  app.add(app.createLabel('Thank you for submitting'));
  return app
}

---

EDIT 2 : I have found solutions for (almost) all the issues... Here is the new code (only doPost part) that provides automatic image scaling for the doc preview. Jpg, png or any other usual image format supported...and shows initial size + weight. I updated the online test form.

The spreadsheet issue has no solution for now, see issue 145, so I use only a link to the image file but this one has no preview as stated in issue 1239 but the document as it works now is a nice and useable workaround (in my opinion :-).

function doPost(e) {
  var app = UiApp.getActiveApplication();
  var ListVal = e.parameter.list1;
  var textVal = e.parameter.TB1;
  var Email = e.parameter.mail;
  var fileBlob = e.parameter.uploadedFile;
  var blob = fileBlob.setContentTypeFromExtension()
  var img = DocsList.createFile(blob);
  try{
  var folder = DocsList.getFolder('photos');
  }catch(e){DocsList.createFolder('photos');var folder = DocsList.getFolder('photos')}
  img.addToFolder(folder);
  img.removeFromFolder(DocsList.getRootFolder());
  var weight = parseInt(img.getSize()/1000);
  var sheet = SpreadsheetApp.openById(submissionSSKey).getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow();
  var targetRange = sheet.getRange(lastRow+1, 1, 1, 4).setValues([[ListVal,textVal,Email,img.getUrl()]]);
  var GDoc = DocumentApp.openByUrl(docurl)
  GDoc.appendTable([['Category : '+ListVal,'Name : '+textVal,'Email : '+Email]])
  var inlineI = GDoc.appendImage(img);
  var width = inlineI.getWidth();
  var newW = width;
  var height = inlineI.getHeight();
  var newH = height;
  var ratio = width/height
  Logger.log('w='+width+'h='+height+' ratio='+ratio);
  if(width>640){
  newW = 640;
  newH = parseInt(newW/ratio);
  }
  inlineI.setWidth(newW).setHeight(newH)
  GDoc.appendParagraph('IMAGE size : '+width+' x '+height+' (eventually) resized to '+newW+' x '+newH+' for PREVIEW ('+weight+' kB)   ');
  GDoc.appendHorizontalRule();
  GDoc.saveAndClose();
  app.add(app.createLabel('Thank you for submitting'));
  return app
}

Solution 3

Thanks to jfreake recent post I ended up solving all the issues, including show the images in the spreadsheet itself. Here is the final code I post in a separate answer for comfort and readbility.

var submissionSSKey = '0AnqSFd3iikE3dGFsUWNpb08zVWx5YjFRckloZ0NFZGc';
var docurl = 'https://docs.google.com/document/d/1E6yoROb52QjICsEbGVXIBdz8KhdFU_5gimWlJUbu8DI/'
var listitems = ['Select a category','Portrait','Landscape','Nude','Night shots','Nature','Various']
var Panelstyle = {'background':'#dddddd','padding':'40px','borderStyle':'solid','borderWidth':'10PX','borderColor':'#bbbbbb'}

function doGet() {
  var app = UiApp.createApplication().setTitle('Photography contest').setStyleAttribute('padding','50PX');
  var panel = app.createFormPanel().setStyleAttributes(Panelstyle).setPixelSize(400, 200);
  var title = app.createHTML('<B>Photography contest</B>').setStyleAttribute('color','grey').setStyleAttribute('fontSize','25PX');
  var grid = app.createGrid(6,2).setId('grid');
  var list1 = app.createListBox().setName('list1').setWidth('130');
   for(var i in listitems){list1.addItem(listitems[i])}    
  var Textbox1 = app.createTextBox().setWidth('150px').setName('TB1');
  var email = app.createTextBox().setWidth('150px').setName('mail');
  var upLoad = app.createFileUpload().setName('uploadedFile');
  var submitButton = app.createSubmitButton('<B>Submit</B>'); 
  var warning = app.createHTML('Please fill in all fields').setStyleAttribute('background','#bbbbbb').setStyleAttribute('fontSize','18px');
  //file upload
  var cliHandler2 = app.createClientHandler()
  .validateLength(Textbox1, 1, 40).validateNotMatches(list1,'Select a category').validateEmail(email).validateNotMatches(upLoad, 'FileUpload')
  .forTargets(submitButton).setEnabled(true)
  .forTargets(warning).setHTML('Now you can submit your form').setStyleAttribute('background','#99FF99').setStyleAttribute('fontSize','12px');

  //Grid layout of items on form
  grid.setWidget(0, 1, title)
      .setText(1, 0, 'Category')
      .setWidget(1, 1, list1.addClickHandler(cliHandler2))
      .setText(2, 0, 'Name')
      .setWidget(2, 1, Textbox1.addClickHandler(cliHandler2))
      .setText(3, 0, 'Email')
      .setWidget(3, 1, email)
      .setText(4, 0, 'Image File')
      .setWidget(4, 1, upLoad.addChangeHandler(cliHandler2))
      .setWidget(5, 0, submitButton)
      .setWidget(5, 1, warning);

  var cliHandler = app.createClientHandler().forTargets(warning).setHTML('<B>PLEASE WAIT WHILE THE FILE IS UPLOADING<B>').setStyleAttribute('background','yellow');
  submitButton.addClickHandler(cliHandler).setEnabled(false);  
  panel.add(grid);
  app.add(panel);
  return app;
}


function doPost(e) {
  var app = UiApp.getActiveApplication();
  var ListVal = e.parameter.list1;
  var textVal = e.parameter.TB1;
  var Email = e.parameter.mail;
  var fileBlob = e.parameter.uploadedFile;
  var blob = fileBlob.setContentTypeFromExtension()
  var img = DocsList.createFile(blob);
  try{
  var folder = DocsList.getFolder('photos');
  }catch(e){DocsList.createFolder('photos');var folder = DocsList.getFolder('photos')}
  img.addToFolder(folder);
  img.removeFromFolder(DocsList.getRootFolder());
  var weight = parseInt(img.getSize()/1000);
  var sheet = SpreadsheetApp.openById(submissionSSKey).getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow();
  var targetRange = sheet.getRange(lastRow+1, 1, 1, 4).setValues([[ListVal,textVal,Email,"https://drive.google.com/uc?export=view&id="+img.getId()]]);
  var imageInsert = sheet.getRange(lastRow+1, 5).setFormula('=image("https://drive.google.com/uc?export=view&id='+img.getId()+'")');
  sheet.setRowHeight(lastRow+1, 80);
  var GDoc = DocumentApp.openByUrl(docurl)
  GDoc.appendTable([['Category : '+ListVal,'Name : '+textVal,'Email : '+Email]])
  var inlineI = GDoc.appendImage(img);
  var width = inlineI.getWidth();
  var newW = width;
  var height = inlineI.getHeight();
  var newH = height;
  var ratio = width/height;
  Logger.log('w='+width+'h='+height+' ratio='+ratio);
  if(width>640){
  newW = 640;
  newH = parseInt(newW/ratio);
  }
  inlineI.setWidth(newW).setHeight(newH)
  GDoc.appendParagraph('IMAGE size : '+width+' x '+height+' (eventually) resized to '+newW+' x '+newH+' for PREVIEW ('+weight+' kB)   ');
  GDoc.appendHorizontalRule();
  GDoc.saveAndClose();
  app.add(app.createLabel('Thank you for submitting'));
  return app
}

links are the same : app SS doc

Solution 4

To get a direct link to the image in the spreadsheet, use the getID function instead of getUrl and prepend the URL to Gdrive.

Change this:

var targetRange = sheet.getRange(lastRow+1, 1, 1, 4)
    .setValues([[ListVal,textVal,Email,img.getUrl()]]);

To this:

var targetRange = sheet.getRange(lastRow+1, 1, 1, 4)
   .setValues([[ListVal,textVal,Email,"https://drive.google.com/uc?export=view&id="+img.getId()]]);

View more solutions

Comments

[Hashim Adel]

Thank you, this will do, I will take it from here

[Serge insas]

to solve the spreadsheet lin knot working use the solution provided in the other post : code like this :var targetRange = sheet.getRange(lastRow+1, 1, 1, 4).setValues([[ListVal,textVal,Email,"https://drive.google.c‌​om/uc?export=view&id‌​="+img.getId()]]); demo form updated, ss results ok from now ;-)

[Raj Pawan Gumdal]

I am getting the following error: TypeError: Cannot read property "parameter" from undefined. (line 47, file "Code"). Any possible ways to fix this?

[Raj Pawan Gumdal]

Where should I embed this script? I tried to create new form and from Tools -> Add script I added these files, but somehow the script is never invoked. How to create form using this script?

[Raj Pawan Gumdal]

Add Script == Script Editor

[Serge insas]

You are probably trying to run doPost on its own, this is not doable... you have to deploy this function as a webapp (save a version first) and use the provided url resulting from the webapp deployment.

[Raj Pawan Gumdal]

Thanks, can you also explain what a docURL and submissionKey is? Sorry, I am naive here, I tried googling around but everything seems to be complicated to me.

[Karen Fisher]

Unfortunatly UiApp deprecated since December 11, 2014

[Serge insas]

?? That's UiApp too ... What's the point to post that in April 2015 ? Je ne pense pas qu'on puisse traduire le mot trombinoscope... C'est typiquement un mot franco-français ça non ? ;-)

[oblab]

You're right Serge, it's UiApp too, sorry. But for some reason your code has stopped working ("Error encountered: An unexpected error occurred"). It worked very well until December, I have using it (by the way, thank you very much !). I think, but i'm not sure, that it's the "DocsList" service that is deprecated : developers.google.com/apps-script/sunset et oui, tu as aussi raison, Trombinoscope est un mot franco-français, j'essayais juste de trouver une traduction approximative pour nos amis anglophones ;-)

[oblab]

I didn't find something to replace Docslist, so i'm not able to save the files in a specific folder, any idea ?

[Serge insas]

please avoid asking question in comments, start a new thread instead if necessary. btw, DriveApp has also a folder object that you can use to save files in.

[Serge insas]

This script is using a couple of deprecated services, at least one of them ceased working (DocsList) so maybe it's not a very good idea to use it now... anyway, I updated the code to make it work again for a while, new script available here : script.google.com/d/…

[Adam]

For anyone reading this in this future, some tips to get it working: (1) docURL is the URL of the Google Doc you created, (2) submission key is the last part of the URL of the Google Sheet you created -- e.g. https://docs.google.com/spreadsheets/d/<submission key>/, (3) make sure the sheet in your Google Sheet is named "Sheet1" or rename it in the code, and (4) use "publish as web app..." to get create the app and test.

[Adam]

To get the URLs to the Google Doc and Google Sheet, either look in your browsers address bar and ignore the "/edit" suffix, etc., or click the "Share" button and use the URL provided there instead.