Upload an image to a Google spreadsheet

问题:

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.

回答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 !

回答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
}

回答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

回答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()]]);

回答5:

EDIT : I upgrade change the code a little bit, because as yyk mentionned, UiApp "doclist" deprecated since December 11, 2014. I use it to create a trombinoscope (i don't know the word in english, group gallery perhaps) in a google doc, people uploaded their picture nd name in a form. I don't use a spreadsheet. Here is the code :

   function doGet(e) {
var app = UiApp.createApplication().setTitle('Trombi');
var panel = app.createFormPanel();
var grid = app.createGrid(3,2).setId('registrationGrid');
var nameLabel = app.createLabel('Name');
var nameTextbox = app.createTextBox().setWidth('150px').setName('Name');
var submitButton = app.createSubmitButton('<B>send</B>'); 
var warning = app.createHTML('<B>Please wait<B>').setStyleAttribute('background','yellow').setVisible(false)
//file upload
var upLoadTypeLabel = app.createLabel('File to Upload');
var upLoad = (app.createFileUpload().setName('thefile'));
//Grid layout of items on form
grid.setWidget(0, 0, nameLabel)
    .setWidget(0, 1, nameTextbox)
    .setWidget(1, 0, upLoadTypeLabel)
    .setWidget(1, 1, upLoad)
    .setWidget(2, 0, submitButton)
    .setWidget(2, 1, warning)
var cliHandler = app.createClientHandler().forTargets(warning).setVisible(true)
submitButton.addClickHandler(cliHandler);  
panel.add(grid);
app.add(panel);
return app;}

function doPost(e) {
var app = UiApp.getActiveApplication();  
var Name = e.parameter.Name;
//app.getElementById('info').setVisible(true).setStyleAttribute('color','red');
 // data returned is a blob for FileUpload widget
var fileBlob = e.parameter.thefile;  
var doc = DriveApp.createFile(fileBlob).setName(Name); 
var doc = DocumentApp.openById('your key');
var body = doc.getBody();
var inlineI = body.appendImage(fileBlob);
  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>200){
  newW = 200;
  newH = parseInt(newW/ratio);
  }
  inlineI.setWidth(newW).setHeight(newH)
  body.appendParagraph(Name);
  body.appendHorizontalRule();
doc.saveAndClose();
app.add(app.createLabel('success')); 
return app
 }