Upload an image to a Google spreadsheet

google-apps-script google-docs-api google-docs

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
}

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 !


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

Related

fitting n variable height images into 3 (similar length) column layout
My app inside iMessage UIActivityViewController [duplicate]
DataTables row.add to specific index
How to detect WiFi tethering state
Kalman filter with varying timesteps
HTTP response code after redirect
Where Does A UIAlertView Live While Not Dismissed
Trouble reading a line using fscanf()
Creating a component with named sub-components?
Ruby method lookup path for an object
Convert exponential to a whole number in PHP [duplicate]
How to Start a Process Unelevated