使用Apps脚本将图像上传到Google Spreadsheet [英] Uploading images to a Google Spreadsheet using Apps Script

问题描述

我不熟悉脚本。我从这里复制了代码将图像上传到Google Spreadsheet

I'm new to scripting. I have copied the code from here Upload image to Google Spreadsheet

原始脚本允许用户通过网络应用程序表单上传1张图片，然后该图片将附加到Google电子表格中的一行和Google文档中。我已经能够修改Web应用程序表单以允许用户上传2张图片，但我无法弄清楚如何将第二张图片加入电子表格和文档中。

The original script allowed a user to upload 1 image via the web app form and then the image would append to a row in a Google spreadsheet and into a Google Doc. I have been able to modify the web app form to allow a user to upload 2 images, but I can't figure out how to get the second image into the spreadsheet and doc.

    var submissionSSKey = 'Google Spreadsheet key';
var docurl = 'Google document URL'
var listitems = ['Select a category','Portrait','Landscape','Other','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(7,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 upLoad1 = app.createFileUpload().setName('uploadedFile1');
  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');


  var cliHandler3 = app.createClientHandler()
  .validateNotMatches(upLoad1, '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, 1, upLoad1.addChangeHandler(cliHandler3))
      .setWidget(6, 0, submitButton)
      .setWidget(6, 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;


  var fileBlob1 = e.parameter.uploadedFile1;
  var blob1 = fileBlob1.setContentTypeFromExtension()
  var img1 = Doclist.createFile(blob1);
  try{
  var folder = DocsList.getFolder('photos');
  }catch(e){DocsList.createFolder('photos');var folder = DocsList.getFolder('photos')}
  img1.addToFolder(folder);
  img1.removeFromFolder(DocsList.getRootFolder());
  var weight = parseInt(img1.getSize()/1000);
  var sheet = SpreadsheetApp.openById(submissionSSKey).getSheetByName('Sheet2');
  var lastRow = sheet.getLastRow();
  var targetRange = sheet.getRange(lastRow+1, 1, 1, 5).setValues([[ListVal,textVal,Email,"https://drive.google.com/uc?export=view&id="+img1.getId()]]);
  var imageInsert = sheet.getRange(lastRow+1, 6).setFormula('=image("https://drive.google.com/uc?export=view&id='+img1.getId()+'")');
  sheet.setRowHeight(lastRow+1, 80);
  var GDoc = DocumentApp.openByUrl(docurl)
  GDoc.appendTable([['Category : '+ListVal,'Name : '+textVal,'Email : '+Email]])
  var inlineI = GDoc.appendImage(img1);
  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
}

推荐答案

由于我写了初始脚本，所以我想我必须在第二个版本上提供帮助： - ）

Since I wrote the initial script I thought I had to help on this second version :-)

以下是新的doPost：

Here is the new doPost :

function doPost(e) {
  var app = UiApp.getActiveApplication();
  var ListVal = e.parameter.list1;
  var textVal = e.parameter.TB1;
  var Email = e.parameter.mail;
  var fileBlob1 = e.parameter.uploadedFile;
  var blob1 = fileBlob1.setContentTypeFromExtension()
  var img1 = DocsList.createFile(blob1);
  var fileBlob2 = e.parameter.uploadedFile1;
  var blob2 = fileBlob2.setContentTypeFromExtension()
  var img2 = DocsList.createFile(blob2);
  try{
  var folder = DocsList.getFolder('photos');
  }catch(e){DocsList.createFolder('photos');var folder = DocsList.getFolder('photos')}
  img1.addToFolder(folder);
  img1.removeFromFolder(DocsList.getRootFolder());
  img2.addToFolder(folder);
  img2.removeFromFolder(DocsList.getRootFolder());
  var weight1 = parseInt(img1.getSize()/1000);
  var weight2 = parseInt(img2.getSize()/1000);
  var sheet = SpreadsheetApp.openById(submissionSSKey).getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow();
  var targetRange = sheet.getRange(lastRow+1, 1, 1, 5).setValues([[ListVal,textVal,Email,"https://drive.google.com/uc?export=view&id="+img1.getId(),"https://drive.google.com/uc?export=view&id="+img2.getId()]]);
  var image1Insert = sheet.getRange(lastRow+1, 6).setFormula('=image("https://drive.google.com/uc?export=view&id='+img1.getId()+'")');
  var image2Insert = sheet.getRange(lastRow+1, 7).setFormula('=image("https://drive.google.com/uc?export=view&id='+img2.getId()+'")');
  sheet.setRowHeight(lastRow+1, 80);
  var GDoc = DocumentApp.openByUrl(docurl)
  GDoc.appendTable([['Category : '+ListVal,'Name : '+textVal,'Email : '+Email]])
  var inlineI = GDoc.appendImage(img1);
  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 ('+weight1+' kB)   ');
  var inlineI = GDoc.appendImage(img2);
  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 ('+weight2+' kB)   ');
  GDoc.appendHorizontalRule();
  GDoc.saveAndClose();
  app.add(app.createLabel('Thank you for submitting'));
  return app
}

和doGet几乎没有任何修改：

and the doGet with few modifications :

var submissionSSKey = '11nxqFSYCqrtF1ukfbuVAJy9bnBnxkLTH0dIAIRKxZxo';
var docurl = 'https://docs.google.com/document/d/1xEQC63NP4D6bebTVeP1u1nagvVm6vE2KFedRuk86VFc/edit'
var listitems = ['Select a category','Portrait','Landscape','Other','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(7,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 upLoad1 = app.createFileUpload().setName('uploadedFile1');
  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');


  var cliHandler3 = app.createClientHandler()
  .validateNotMatches(upLoad1, '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, 1, upLoad1.addChangeHandler(cliHandler3))
      .setWidget(6, 0, submitButton)
      .setWidget(6, 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;
}

这篇关于使用Apps脚本将图像上传到Google Spreadsheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！