How do I get a dynamic Javascript array into Googl

2019-06-12 09:55发布

站内文章 / JavaScript
32 0 0

问题:

I have made a Javascript code as a mash up from the following scripts:

https://sites.google.com/site/appsscripttutorial/miscellaneous/creating-form-elements-dynamically-using-google-apps-script-gas

Google Apps Script Create form with file upload

When I use JSON.Stringify to verify my results I get this:

{"poNumber":"5555","vendor":"Walmart","reference":"1131","paidBy":"BofA Card","total":"15","poLines":[{"Qty":"2","Desc":"Streamers","uPrice":"6","xPrice":"10"}]}

I could work with that, however when I try to add the data set to a appendRow function, I get the following output into the bottom row of my spreadsheet:

{total=15, poLines=[Ljava.lang.Object;@7e3b6f9b, vendor=Walmart, poNumber=23454531, paidBy=Capital One Card, reference=1131}

How can I get the spreadsheet to read the contents of the Ljava.lang.Object?

here's the current code, with the code in question at the bottom:

    function doGet(e) {
  var app = UiApp.createApplication().setTitle('PO Processing');
  var panel = app.createFormPanel();
  var grid = app.createGrid(8,2).setId('poData');
  var poNumberLB = app.createLabel('PO Number');
  var poNumberTB = app.createTextBox().setId('poNumber').setWidth('150px').setName('poNumber');
  var vendorLB = app.createLabel('Vendor');
  var vendorTB = app.createTextBox().setId('vendor').setWidth('150px').setName('vendor'); 
  var referenceLB = app.createLabel('Reference/Invoice Number');
  var referenceTB = app.createTextBox().setId('reference').setWidth('150px').setName('reference'); 
  var paidByLB = app.createLabel('Paid By');
  var paidByTB = app.createListBox().setName('paidBy').setWidth('120px').setName('paidBy');
      paidByTB.addItem('On Acount');    
      paidByTB.addItem('Cash');
      paidByTB.addItem('Amex Card');  
      paidByTB.addItem('BofA Card');
      paidByTB.addItem('Capital One Card');
      paidByTB.addItem('Chase Card'); 
  var totalLB = app.createLabel('Total Invoice');
  var totalTB = app.createTextBox().setId('total').setWidth('150px').setName('total');   
  var PODetailsLabel = app.createLabel('PO Details'); var grid = app.createGrid(8,2).setId('poData');
  var poNumberLB = app.createLabel('PO Number');
  var poNumberTB = app.createTextBox().setId('poNumber').setWidth('150px').setName('poNumber');
  var vendorLB = app.createLabel('Vendor');
  var vendorTB = app.createTextBox().setId('vendor').setWidth('150px').setName('vendor'); 
  var referenceLB = app.createLabel('Reference/Invoice Number');
  var referenceTB = app.createTextBox().setId('reference').setWidth('150px').setName('reference'); 
  var paidByLB = app.createLabel('Paid By');
  var paidByTB = app.createListBox().setName('paidBy').setWidth('120px').setName('paidBy');
      paidByTB.addItem('On Acount');    
      paidByTB.addItem('Cash');
      paidByTB.addItem('Amex Card');  
      paidByTB.addItem('BofA Card');
      paidByTB.addItem('Capital One Card');
      paidByTB.addItem('Chase Card'); 
  var totalLB = app.createLabel('Total Invoice');
  var totalTB = app.createTextBox().setId('total').setWidth('150px').setName('total');   
  var PODetailsLabel = app.createLabel('PO Details');
  var table = app.createFlexTable().setId('table').setTag('0'); //Here tag will count the number of PO Lines
 //  Write the header for the table
  var headerArray = ['Quantity', 'Description', 'Unit Price', 'Extended Price'];
  for(var i=0; i<headerArray.length; i++){
    table.setWidget(0, i, app.createLabel(headerArray[i]));
  }

  //Add the first row of form elelments to input PO information
  addPORow(app);
  var submitButton = app.createButton('<B>Submit</B>'); 
  var warning = app.createHTML('<B>PLEASE WAIT WHILE DATA IS UPLOADING<B>').setStyleAttribute('background','yellow').setVisible(false)
  //file upload
  var upLoadLabel = app.createLabel('Receipt Upload');
  var upLoad = (app.createFileUpload().setName('thefile'));

  //Grid layout of items on form
  grid.setWidget(0, 0, poNumberLB)
      .setWidget(0, 1, poNumberTB)
      .setWidget(1, 0, vendorLB)
      .setWidget(1, 1, vendorTB)
      .setWidget(2, 0, referenceLB)
      .setWidget(2, 1, referenceTB)
      .setWidget(3, 0, paidByLB)
      .setWidget(3, 1, paidByTB)
      .setWidget(4, 0, totalLB)
      .setWidget(4, 1, totalTB)
      .setWidget(5, 0, PODetailsLabel)
      .setWidget(5, 1, table)
      .setWidget(6, 0, upLoadLabel)
      .setWidget(6, 1, upLoad)
      .setWidget(7, 0, submitButton)
      .setWidget(7, 1, warning)

  var cliHandler = app.createClientHandler().forTargets(warning).setVisible(true)
  submitButton.addClickHandler(cliHandler); 

  var handler = app.createServerHandler('_processSubmittedData');
  handler.addCallbackElement(panel);
  submitButton.addMouseUpHandler(handler);
  panel.add(grid)
  app.add(panel);
  return app;
}

function addPORow(app){
  var table = app.getElementById('table');
  var tag = parseInt(table.getTag());
  var numRows = tag+1;
  if(numRows >1){
    table.removeCell(numRows-1, 5);
    table.removeCell(numRows-1, 4);
  }
  table.setWidget(numRows, 0, app.createTextBox().setId('Qty'+numRows).setName('Qty'+numRows));
  table.setWidget(numRows, 1, app.createTextBox().setId('Desc'+numRows).setName('Desc'+numRows));
  table.setWidget(numRows, 2, app.createTextBox().setId('uPrice'+numRows).setName('uPrice'+numRows));
  table.setWidget(numRows, 3, app.createTextBox().setId('xPrice'+numRows).setName('xPrice'+numRows));  
  table.setTag(numRows.toString());
  addButtons(app);
}

function addButtons(app){
  var table = app.getElementById('table');
  var numRows = parseInt(table.getTag());

  //Create handler to add/remove row
  var addRemoveRowHandler = app.createServerHandler('_addRemoveRow');
  addRemoveRowHandler.addCallbackElement(table);

 //Add row button and handler
  var addRowBtn = app.createButton('+').setId('addOne').setTitle('Add row');
  table.setWidget(numRows, 4, addRowBtn);
  addRowBtn.addMouseUpHandler(addRemoveRowHandler);

  //remove row button and handler
  var removeRowBtn = app.createButton('-').setId('removeOne').setTitle('Remove row');
  table.setWidget(numRows, 5, removeRowBtn);
  removeRowBtn.addMouseUpHandler(addRemoveRowHandler);
}

function _addRemoveRow(e){
  var app = UiApp.getActiveApplication();
  var table = app.getElementById('table');
  var tag = parseInt(e.parameter.table_tag);
  var source = e.parameter.source;
  if(source == 'addOne'){
    table.setTag(tag.toString());
    addPORow(app);
  }
  else if(source == 'removeOne'){
    if(tag > 1){
      //Dcrement the tag by one
      var numRows = tag-1;
      table.removeRow(tag);
      //Set the new tag of the table
      table.setTag(numRows.toString());
      //Add buttons in previous row
      addButtons(app); 
    }
  }
  return app;
}


function _processSubmittedData(e){
  var app = UiApp.getActiveApplication();
  var result = {};
  result.poNumber = e.parameter.poNumber;
  result.vendor = e.parameter.vendor;
  result.reference = e.parameter.reference;
  result.paidBy = e.parameter.paidBy;
  result.total = e.parameter.total;
   var numPOLines = parseInt(e.parameter.table_tag);
   result.poLines = []; 
  //PO info array
  for(var i=1; i<=numPOLines; i++){
    var poLine = {};
    poLine.Qty = e.parameter['Qty'+i];
    poLine.Desc = e.parameter['Desc'+i];
    poLine.uPrice = e.parameter['uPrice'+i];
    poLine.xPrice = e.parameter['xPrice'+i];
    result.poLines.push(poLine);
  }
    // Write to spreadsheet
 var ss =  SpreadsheetApp.openById('*****samplesheet*****');
 var sheet = ss.getSheetByName('POData');
    sheet.appendRow([result]);

  // Verify String Data Set
 //var poData = JSON.stringify(result);
 //var html = app.createHTML(poData); 
 //app.add(html);

 //  File uploader to add later
 //  var fileBlob = e.parameter.thefile;
 //  var doc = DocsList.createFile(fileBlob);

    return app;

 }

回答1:

In the end I just had the JSON.stringify argument in the wrong place. This is a nice script because I can use a standard form for "requests for purchase" and this posts to a different sheet in the same workbook. It lets me add a dynamic number of PO Lines allowing Google Spreadsheets to act like a database. It also renames the uploaded file to include poNumber and invoice number, as well as specify a receipt folder that the invoices are kept in.

I was able to get it to write to the spreadsheet and filter out the details using an intermediate spreadsheet with the following formulas.

Parse Data:

=split(index(POData!A:A),"[]},{")

And Filter Data: =iferror(mid(index('POfilter1'!A:BZ),find(":",index('POfilter1'!A:BZ))+2,len(index('POfilter1'!A:BZ))-find(":",index('POfilter1'!A:BZ))-2),)

Here is the final script to post at the end:

function doGet(e) {
  var app = UiApp.createApplication().setTitle('PO Processing');
  var panel = app.createFormPanel();
  var grid = app.createGrid(9,2).setId('poData');
  var poNumberLB = app.createLabel('PO Number');
  var poNumberTB = app.createTextBox().setId('poNumber').setWidth('150px').setName('poNumber');
  var vendorLB = app.createLabel('Vendor');
  var vendorTB = app.createTextBox().setId('vendor').setWidth('150px').setName('vendor'); 
  var referenceLB = app.createLabel('Reference/Invoice Number');
  var referenceTB = app.createTextBox().setId('reference').setWidth('150px').setName('reference'); 

  //Manage Payment Info Here:
  var paidByLB = app.createLabel('Paid By');
  var paidByTB = app.createListBox().setName('paidBy').setWidth('120px').setName('paidBy');
      paidByTB.addItem('On Acount');    
      paidByTB.addItem('Cash');
      paidByTB.addItem('Amex Card');  
      paidByTB.addItem('BofA Card');
      paidByTB.addItem('Captital One Card');
      paidByTB.addItem('Chase Card');  

  var totalLB = app.createLabel('Total Invoice');
  var totalTB = app.createTextBox().setId('total').setWidth('150px').setName('total');   
  var shippingLB = app.createLabel('Shipping Costs');
  var shippingTB = app.createTextBox().setId('shipping').setWidth('150px').setName('shipping');   

  var PODetailsLabel = app.createLabel('PO Details');
  var table = app.createFlexTable().setId('table').setTag('0'); //Here tag will count the number of PO Lines
 //  Write the header for the table
  var headerArray = ['Quantity', 'Description', 'Unit Price', 'Tax Exempt?'];
  for(var i=0; i<headerArray.length; i++){
    table.setWidget(0, i, app.createLabel(headerArray[i]));
  }

  //Add the first row of form elelments to input PO information
  addPORow(app);
  var submitButton = app.createSubmitButton('<B>Submit</B>'); 
  var warning = app.createHTML('<B>PLEASE WAIT WHILE DATA IS UPLOADING<B>').setStyleAttribute('background','yellow').setVisible(false)
  //file upload
  var upLoadLabel = app.createLabel('Receipt Upload');
  var upLoad = (app.createFileUpload().setName('thefile'));

  //Grid layout of items on form
  grid.setWidget(0, 0, poNumberLB)
      .setWidget(0, 1, poNumberTB)
      .setWidget(1, 0, vendorLB)
      .setWidget(1, 1, vendorTB)
      .setWidget(2, 0, referenceLB)
      .setWidget(2, 1, referenceTB)
      .setWidget(3, 0, paidByLB)
      .setWidget(3, 1, paidByTB)
      .setWidget(4, 0, shippingLB)
      .setWidget(4, 1, shippingTB)
      .setWidget(5, 0, totalLB)
      .setWidget(5, 1, totalTB)
      .setWidget(6, 0, PODetailsLabel)
      .setWidget(6, 1, table)
      .setWidget(7, 0, upLoadLabel)
      .setWidget(7, 1, upLoad)
      .setWidget(8, 0, submitButton)
      .setWidget(8, 1, warning)

  var cliHandler = app.createClientHandler().forTargets(warning).setVisible(true)
  submitButton.addClickHandler(cliHandler); 

  var handler = app.createServerHandler('_processSubmittedData');
  handler.addCallbackElement(panel);
  submitButton.addMouseUpHandler(handler);
  panel.add(grid)
  app.add(panel);
  return app;
}

function addPORow(app){
  var table = app.getElementById('table');
  var tag = parseInt(table.getTag());
  var numRows = tag+1;
  if(numRows >1){
    table.removeCell(numRows-1, 5);
    table.removeCell(numRows-1, 4);
  }
  table.setWidget(numRows, 0, app.createTextBox().setId('Qty'+numRows).setName('Qty'+numRows));
  table.setWidget(numRows, 1, app.createTextBox().setId('Desc'+numRows).setName('Desc'+numRows));
  table.setWidget(numRows, 2, app.createTextBox().setId('uPrice'+numRows).setName('uPrice'+numRows));
  table.setWidget(numRows, 3, app.createCheckBox().setId('taxExempt'+numRows).setName('taxExempt'+numRows));  
  table.setTag(numRows.toString());
  addButtons(app);
}

function addButtons(app){
  var table = app.getElementById('table');
  var numRows = parseInt(table.getTag());

  //Create handler to add/remove row
  var addRemoveRowHandler = app.createServerHandler('_addRemoveRow');
  addRemoveRowHandler.addCallbackElement(table);

 //Add row button and handler
  var addRowBtn = app.createButton('+').setId('addOne').setTitle('Add row');
  table.setWidget(numRows, 4, addRowBtn);
  addRowBtn.addMouseUpHandler(addRemoveRowHandler);

  //remove row button and handler
  var removeRowBtn = app.createButton('-').setId('removeOne').setTitle('Remove row');
  table.setWidget(numRows, 5, removeRowBtn);
  removeRowBtn.addMouseUpHandler(addRemoveRowHandler);
}

function _addRemoveRow(e){
  var app = UiApp.getActiveApplication();
  var table = app.getElementById('table');
  var tag = parseInt(e.parameter.table_tag);
  var source = e.parameter.source;
  if(source == 'addOne'){
    table.setTag(tag.toString());
    addPORow(app);
  }
  else if(source == 'removeOne'){
    if(tag > 1){
      //Dcrement the tag by one
      var numRows = tag-1;
      table.removeRow(tag);
      //Set the new tag of the table
      table.setTag(numRows.toString());
      //Add buttons in previous row
      addButtons(app); 
    }
  }
  return app;
}


function _processSubmittedData(e){
  var app = UiApp.getActiveApplication();
  var result = {};
  result.poNumber = e.parameter.poNumber;
  result.vendor = e.parameter.vendor;
  result.reference = e.parameter.reference;
  result.paidBy = e.parameter.paidBy;
  result.total = e.parameter.total;
  result.shipping = e.parameter.shipping;
   var numPOLines = parseInt(e.parameter.table_tag);
   result.poLines = []; 
  //PO info array
  for(var i=1; i<=numPOLines; i++){
    var poLine = {};
    poLine.Qty = e.parameter['Qty'+i];
    poLine.Desc = e.parameter['Desc'+i];
    poLine.uPrice = e.parameter['uPrice'+i];
    poLine.taxExempt = e.parameter['taxExempt'+i];
    result.poLines.push(poLine);
  }

  // Write to spreadsheet
 var poData = JSON.stringify(result);
 var ss =  SpreadsheetApp.openById('***Google Spreadsheet ID***');
 var sheet = ss.getSheetByName('POData');
     sheet.appendRow([poData]);
    return app;  
}

 //  File uploader
function doPost(e) {
  poNumber = e.parameter.poNumber;
  reference = e.parameter.reference;
  name = poNumber+"-"+reference;
  nameLength = name.length;
  if(nameLength ==1) {
   }
  else {
    fileName = e.parameter.thefile.name;
    var fileBlob = e.parameter.thefile;

    fileNameLength = fileName.length;
    if(fileNameLength !=0) {
      var fileDocName = name+"-"+e.parameter.thefile.name;
      var doc = DocsList.createFile(fileBlob);
      doc.rename(fileDocName);
      var folder = DocsList.getFolderById('***GDrive Folder ID***');
      doc.addToFolder(folder);
      doc.removeFromFolder(DocsList.getRootFolder());
    }
  }
 }


标签： javascript google-apps-script google-sheets
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~