如何将动态Javascript数组转换为Google Spreadsheets？ [英] How do I get a dynamic Javascript array into Google Spreadsheets?
本文介绍了如何将动态Javascript数组转换为Google Spreadsheets？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我从以下脚本中将Javascript代码作为混搭使用：
当我使用JSON.Stringify进行验证时我的结果我得到这个：
{poNumber：5555，vendor：Walmart，reference ：1131，paidBy：BofA Card，total：15，poLines：[{Qty：2，Desc：飘带，uPrice ，xPrice：10}]}
我可以使用它，但是当我尝试添加数据集到一个appendRow函数，我得到了以下输出到我的底部行电子表格：
{total = 15，poLines = [Ljava.lang.Object; @ 7e3b6f9b，vendor = Walmart，poNumber = 23454531 ，paidBy = Capital One Card，reference = 1131}
如何让电子表格读取内容的Ljava.lang.Object？
这里是当前的代码，代码在底部：
函数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'）; //这里的标签会统计PO行的数量
//为表格写头
var headerArray = ['Quantity'，'Description'，'单价'，'扩展价格'];
for（var i = 0; i< headerArray.length; i ++）{
table.setWidget（0，i，app.createLabel（headerArray [i]））;
}
//添加第一行表单元素以输入采购订单信息
addPORow（app）;
var submitButton = app.createButton（'< B>提交< / B>'）;
var warning = app.createHTML（'< B>请在数据上传时等待'）。setStyleAttribute（'background'，'yellow'）。setVisible（false）
// file上传
var upLoadLabel = app.createLabel（'收据上传'）;
var upLoad =（app.createFileUpload（）。setName（'thefile'））;
//格式
的网格布局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，表）
.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）;
返回应用程序;
}
函数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）;
}
函数addButtons（app）{
var table = app.getElementById（'table'）;
var numRows = parseInt（table.getTag（））;
//创建处理程序以添加/删除行
var addRemoveRowHandler = app.createServerHandler（'_ addRemoveRow'）;
addRemoveRowHandler.addCallbackElement（table）;
//添加行按钮和处理程序
var addRowBtn = app.createButton（'+'）.setId（'addOne'）。setTitle（'Add row'）;
table.setWidget（numRows，4，addRowBtn）;
addRowBtn.addMouseUpHandler（addRemoveRowHandler）;
//删除行按钮和处理程序
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）{
//将标签放一个
var numRows =标签-1;
table.removeRow（tag）;
//设置表格的新标签
table.setTag（numRows.toString（））;
//在前一行添加按钮
addButtons（app）;
}
}
返回应用;
函数_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信息数组
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）;
}
//写入电子表格
var ss = SpreadsheetApp.openById（'***** samplesheet *****'）;
var sheet = ss.getSheetByName（'POData'）;
sheet.appendRow（[result]）;
//验证字符串数据集
// var poData = JSON.stringify（result）;
// var html = app.createHTML（poData）;
//app.add(html）;
//文件上传器以后添加
// var fileBlob = e.parameter.thefile;
// var doc = DocsList.createFile（fileBlob）;
返回应用;
解决方案
结束我刚刚在错误的地方有JSON.stringify参数。这是一个很好的脚本，因为我可以使用购买请求的标准表单并将其张贴到同一工作簿中的其他表单。它可以让我添加动态数量的PO行，让Google Spreadsheets像数据库一样行事。它还将上传的文件重命名为包含poNumber和发票号码，并指定保存发票的收据文件夹。
我能够将其写入到电子表格并使用具有以下公式的中间电子表格过滤出详细信息。
解析数据：
= split（index（POData！A ：A），[]}，{）
过滤数据： = IFERROR（中期（指数（ 'POfilter1' A：BZ），找到（ ：，索引（ 'POfilter1' A：！BZ））+ 2，LEN（指数（ 'POfilter1' A：BZ）） -find（：，index（'POfilter1'！A：BZ）） - 2），）
在最后发帖：
函数doGet（e）{
var app = UiApp.createApplication（）。setTitle （'PO处理'）;
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'）;
//在此处管理付款信息：
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'）; //这里的标签会计算采购订单行的数量
//为表格写头
var headerArray = ['Quantity'，'Description'，'单价'，'Tax Exempt？'] ;
for（var i = 0; i< headerArray.length; i ++）{
table.setWidget（0，i，app.createLabel（headerArray [i]））;
}
//添加第一行表单元素以输入采购订单信息
addPORow（app）;
var submitButton = app.createSubmitButton（'< B>提交< / B>'）;
var warning = app.createHTML（'< B>请在数据上传时等待'）。setStyleAttribute（'background'，'yellow'）。setVisible（false）
// file上传
var upLoadLabel = app.createLabel（'收据上传'）;
var upLoad =（app.createFileUpload（）。setName（'thefile'））;
//格式
的网格布局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）;
返回应用程序;
}
函数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）;
}
函数addButtons（app）{
var table = app.getElementById（'table'）;
var numRows = parseInt（table.getTag（））;
//创建处理程序以添加/删除行
var addRemoveRowHandler = app.createServerHandler（'_ addRemoveRow'）;
addRemoveRowHandler.addCallbackElement（table）;
//添加行按钮和处理程序
var addRowBtn = app.createButton（'+'）.setId（'addOne'）。setTitle（'Add row'）;
table.setWidget（numRows，4，addRowBtn）;
addRowBtn.addMouseUpHandler（addRemoveRowHandler）;
//删除行按钮和处理程序
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）{
//将标签放一个
var numRows =标签-1;
table.removeRow（tag）;
//设置表格的新标签
table.setTag（numRows.toString（））;
//在前一行添加按钮
addButtons（app）;
}
}
返回应用;
函数_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信息数组
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）;
}
//写入电子表格
var poData = JSON.stringify（result）;
var ss = SpreadsheetApp.openById（'*** Google Spreadsheet ID ***'）;
var sheet = ss.getSheetByName（'POData'）;
sheet.appendRow（[poData]）;
返回应用程序;
}
//文件上传器
函数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（））;
}
}
}
I have made a Javascript code as a mash up from the following scripts:
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;
}
解决方案
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 Spreadsheets？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
