来自多个表单的多个电子表格行 [英] Multiple spreadsheet rows from multiple forms

问题描述

好的，正在取得进展：我已经成功地将表单条目写入电子表格，并且它正在工作，除了最后一个条目被输入两次。任何想法我做错了任何人？

修改后的代码是：

函数InsertDataInSheet（e）//通过单击submit {var app = UiApp.getActiveApplication（）;将数据插入到电子表格中的函数//获取要输入的行数var num = parseInt（e.parameter.table_tag）; var num = num + 1; //设置增量step through（var i = 1; i< num; i ++）{//声明varialbe字段以从var user = Session.getActiveUser（）。getEmail（）; var date = e.parameter ['DateBox'+ i]; var location = e.parameter ['LocationListBox'+ i]; var source = e.parameter ['SourceListBox'+ i]; var reporter = e.parameter ['ReporterTextBox'+ i]; var priority = e.parameter ['PriorityListBox'+ i]; var hazard = e.parameter ['HazardListBox'+ i]; var details = e.parameter ['DetailsTextBox'+ i]; var description = e.parameter ['DescriptionTextBox'+ i]; var timeStamp = new Date（）; //确定需要关闭的日期if（priority === '02 - WITHIN 24-48 HOURS'）{var dateTemp = new Date（date）; dateTemp.setDate（dateTemp.getDate（）+ 2）; var actiondate = dateTemp; } if（priority === '03 - WITHIN 1 WEEK'）{var dateTemp = new Date（date）;} dateTemp.setDate（dateTemp.getDate（）+ 7）; var actiondate = dateTemp; } if（priority === '04 - WITHIN 1 MONTH'）{var dateTemp = new Date（date）;} dateTemp.setDate（dateTemp.getDate（）+ 31）; var actiondate = dateTemp; } if（priority === '05 - WITHIN 3 MONTHS'）{var dateTemp = new Date（date）;} dateTemp.setDate（dateTemp.getDate（）+ 90）; var actiondate = dateTemp; } if（priority === '06 - FOR MANAGEMENT DISCUSSION'）{var dateTemp = new Date（date）;} dateTemp.setDate（dateTemp.getDate（）+ 365）; var actiondate = dateTemp; } //建立电子邮件地址//声明正确的范围以获取值var LocationSheet = SpreadsheetApp.openById（itemSpreadsheetKey）.getSheetByName（LocationCodes）//从中央维护部门开始var email00 = LocationSheet.getRange（33,5）.getValue （）//后面跟着其他电子邮件，因为它们出现var email01 = LocationSheet.getRange（3,5）.getValue（）; var email02 = LocationSheet.getRange（4,5）.getValue（）; var email03 = LocationSheet.getRange（5,5）.getValue（）; var email04 = LocationSheet.getRange（6,5）.getValue（）; var email05 = LocationSheet.getRange（7,5）.getValue（）; var email06 = LocationSheet.getRange（8,5）.getValue（）; var email07 = LocationSheet.getRange（9,5）.getValue（）; var email08 = LocationSheet.getRange（10,5）.getValue（）; var email09 = LocationSheet.getRange（11,5）.getValue（）; var email10 = LocationSheet.getRange（12,5）.getValue（）; var email11 = LocationSheet.getRange（13,5）.getValue（）; var email12 = LocationSheet.getRange（14,5）.getValue（）; var email13 = LocationSheet.getRange（15,5）.getValue（）; var email14 = LocationSheet.getRange（16,5）.getValue（）; var email15 = LocationSheet.getRange（17,5）.getValue（）; var email16 = LocationSheet.getRange（18,5）.getValue（）; var email17 = LocationSheet.getRange（19,5）.getValue（）; var email18 = LocationSheet.getRange（20,5）.getValue（）; var email19 = LocationSheet.getRange（21,5）.getValue（）; var email20 = LocationSheet.getRange（22,5）.getValue（）; //声明正确的Depots来检查var LocationSheet = SpreadsheetApp.openById（itemSpreadsheetKey）.getSheetByName（LocationCodes）; var depot01 = LocationSheet.getRange（3,4）.getValue（）; var depot02 = LocationSheet.getRange（4,4）.getValue（）; var depot03 = LocationSheet.getRange（5,4）.getValue（）; var depot04 = LocationSheet.getRange（6,4）.getValue（）; var depot05 = LocationSheet.getRange（7,4）.getValue（）; var depot06 = LocationSheet.getRange（8,4）.getValue（）; var depot07 = LocationSheet.getRange（9,4）.getValue（）; var depot08 = LocationSheet.getRange（10,4）.getValue（）; var depot09 = LocationSheet.getRange（11,4）.getValue（）; var depot10 = LocationSheet.getRange（12,4）.getValue（）; var depot11 = LocationSheet.getRange（13,4）.getValue（）; var depot12 = LocationSheet.getRange（14,4）.getValue（）; var depot13 = LocationSheet.getRange（15,4）.getValue（）; var depot14 = LocationSheet.getRange（16,4）.getValue（）; var depot15 = LocationSheet.getRange（17,4）.getValue（）; var depot16 = LocationSheet.getRange（18,4）.getValue（）; var depot17 = LocationSheet.getRange（19,4）.getValue（）; var depot18 = LocationSheet.getRange（20,4）.getValue（）; var depot19 = LocationSheet.getRange（21,4）.getValue（）; var depot20 = LocationSheet.getRange（22,4）.getValue（）; //如果源被记录为'08 - 维护请求系统'，则收件人为维护部件（源===08 - 维护请求系统）{var recipient = email00; //或者depot列出} else if（location === depot01）{var recipient = email01; } else if（location === depot02）{var recipient = email02; } else if（location === depot03）{var recipient = email03; } else if（location === depot04）{var recipient = email04; } else if（location === depot05）{var recipient = email05; } else if（location === depot06）{var recipient = email06; } else if（location === depot07）{var recipient = email07; } else if（location === depot08）{var recipient = email08; } else if（location === depot09）{var recipient = email09; } else if（location === depot10）{var recipient = email10; } else if（location === depot11）{var recipient = email11; } else if（location === depot12）{var recipient = email12; } else if（location === depot13）{var recipient = email13; } else if（location === depot14）{var recipient = email14; } else if（location === depot15）{var recipient = email15; } else if（location === depot16）{var recipient = email16; } else if（location === depot17）{var recipient = email17; } else if（location === depot18）{var recipient = email18; } else if（location === depot19）{var recipient = email19; } else if（location === depot20）{var recipient = email20; } else {//如果没有提供代码，则发送一封电子邮件给错误catch all var recipient = email00; //根据需要更改} var sheet = SpreadsheetApp.openById（itemSpreadsheetKey）.getSheetByName（'LOG'）; var lastRow = sheet.getLastRow（）; var lrp1 = lastRow + 1 var targetRange = sheet.getRange（lastRow + 1,1,1,12）.setValues（[[timeStamp，date，source，location，reporter，user，hazard，details， actiondate]]）; } var sheet = SpreadsheetApp.openById（itemSpreadsheetKey）.getSheetByName（LOG）; var lastRow = sheet.getLastRow（）; var lrp1 = lastRow + 1 //将[getRange（lastRow + 1，1，1，**）]整数修改为reflet正在写入的页眉数量如果添加了更多var targetRange = sheet.getRange（lastRow + 1,1,1， 12）.setValues（[[的timeStamp，日期，源，位置，记者，用户，危险，细节，描述，优先级，接收方，actiondate]]）; var Body ='在['+ location +']记录了一个新的['+ source +']日志条目，列为['+ hazard +']。此表单由['+ user +']用时间戳['+ timeStamp +']提交。'}

我还没有更进一步，所以我把问题分解为两个。这是第1部分。我的代码仍然失败，这是因为（如果我正确地理解桑迪（再次感谢btw））没有循环。

代码写什么我需要按照正确的顺序，我需要它，它不会为每个条目写一个新行 - 我只写第一行。

任何帮助非常感谢。

伪代码如下所示：

1. 创建表单填写嵌入到网站中的表单。

创建+& - 按钮用于根据需要添加或删除行。 点击提交点击后，打开电子表格并为表单中创建的每一行写入一行新的数据。

2. 
   $ b
提交后创建通知页面。我到目前为止的代码是：



  //电子表格键用于访问正确的电子表格。它位于URL 
之后的/ d /之后和/ edit之前。var itemSpreadsheetKey ='1mgp5d6gZydrn-bI6KfCNNOsIj9sh0dLJMzVq4Al4ypY'; 
 var LogSheet = SpreadsheetApp.openById（itemSpreadsheetKey）.getSheetByName（Log）; 
 var LocationSheet = SpreadsheetApp.openById（itemSpreadsheetKey）.getSheetByName（LocationCodes）; 
 var HazardSheet = SpreadsheetApp.openById（itemSpreadsheetKey）.getSheetByName（HazardCodes）; 
 var SourceSheet = SpreadsheetApp.openById（itemSpreadsheetKey）.getSheetByName（SourceCodes）; 
 
 
函数doGet（）//'doGet'函数从小工具中嵌入的URL中检索代码
 {
 var app = UiApp.createApplication（） ; 
 var panel = app.createVerticalPanel（）; 
 var table = app.createFlexTable（）。setId（'table'）。setTag（'0'）。setBorderWidth（0）; 
 
 //写下表格的标题
 var headerArray = ['DATE'，'DEPOT / SITE'，'REPORTER'，'SOURCE CODE'，'HAZARD CODE'，'Brief详情'，'完整描述'，'优先']; 
 for（var i = 0; i< headerArray.length; i ++）
 {
 table.setWidget（0，i，app.createLabel（headerArray [i]））; 
} 
 
 //添加第一行表单元素以输入成员信息
 addMemberRow（app）; 
 
 //添加一个按钮提交信息
 var button = app.createSubmitButton（'CLICK TO SUBMIT'）; 
 
 //调用InsertDataInSheet例程
 var handler = app.createServerHandler（'InsertDataInSheet'）; 
 handler.addCallbackElement（panel）; 
 button.addMouseUpHandler（handler）; 
 panel.add（table）
 .add（button）; 
 app.add（panel）; 
返回应用程序; 
} 
 
 
函数InsertDataInSheet（e）//通过单击提交按钮
 var app = UiApp在表单中插入数据的函数。 getActiveApplication（）; 
 //从最后一行数据获取输入值
 var num = parseInt（e.parameter.table_tag）; 
 var num = num + 1; 
 //设置递增值以逐步通过
 for（var i = 1; i< num; i ++）
 {
 //声明变量字段以从$ b收集数据$ b var user = Session.getActiveUser（）。getEmail（）; 
 var date = e.parameter ['DateBox'+ i]; 
 var location = e.parameter ['LocationListBox'+ i]; 
 var source = e.parameter ['SourceListBox'+ i]; 
 var reporter = e.parameter ['ReporterTextBox'+ i]; 
 var priority = e.parameter ['PriorityListBox'+ i]; 
 var hazard = e.parameter ['HazardListBox'+ i]; 
 var details = e.parameter ['DetailsTextBox'+ i]; 
 var description = e.parameter ['DescriptionTextBox'+ i]; 
 var user = Session.getUser（）。getUserLoginId（）; 
 var timeStamp = new Date（）; 
 
 //决定日期，这需要以
结束if（priority === '02  -  WITHIN 24-48 HOURS'）
 {
 var dateTemp =新日期（日期）; 
 dateTemp.setDate（dateTemp.getDate（）+ 2）; 
 var actiondate = dateTemp; 
 
 if（priority == '03  -  WITHIN 1 WEEK'）
 {
 var dateTemp = new Date（date）; 
 dateTemp.setDate（dateTemp.getDate（）+ 7）; 
 var actiondate = dateTemp; 
 
 if（priority == '04  -  WITHIN 1 MONTH'）
 {
 var dateTemp = new Date（date）; 
 dateTemp.setDate（dateTemp.getDate（）+ 31）; 
 var actiondate = dateTemp; 
} 
 if（priority == '05  -  WITHIN 3 MONTHS'）
 {
 var dateTemp = new Date（date）; 
 dateTemp.setDate（dateTemp.getDate（）+ 90）; 
 var actiondate = dateTemp; 
} 
 if（priority == '06  -  FOR MANAGEMENT DISCUSSION'）
 {
 var actiondate =N / A; 
} 
 
 app = UiApp.getActiveApplication（）。remove（0）; 
 app.createVerticalPanel（）
 .setId（'info'）
 .setVisible（true）
 .setStyleAttribute（'left'，0）
 .setStyleAttribute（' （''''''）'
 .setStyleAttribute（' 
 .setHeight（'400px'）
 .setStyleAttribute（'text-align'，'center'）
 .setBorderWidth（0）
 .setWidth（'500px'）; 
 app.add（app.createLabel（''））; 
 app.add（app.createLabel（'Thank you。Your form has been submitted to the Proactive / Reactive Hazard Logging database and'+ recipient +'（the Health& Safety Site Coordinator）has been alerts。'）） ; 
 app.add（app.createLabel（'这封电子邮件的副本已发送到'+ user +'，时间戳'+ timeStamp +'，如果需要跟进，应该引用该时间戳。'））; 
 app.add（app.createLabel（'请刷新此页面添加提交另一个表单，或关闭页面退出'））; 
返回app.close（）; 
} 
 //建立电子邮件地址
 //声明正确的范围以获取值
 var LocationSheet = SpreadsheetApp.openById（itemSpreadsheetKey）.getSheetByName（LocationCodes）
 //从中央维护部门开始
 var email00 = LocationSheet.getRange（33,5）.getValue（）
 //后跟其他电子邮件，因为它们出现
 var email01 = LocationSheet.getRange（ 3,5）.getValue（）; 
 var email02 = LocationSheet.getRange（4,5）.getValue（）; 
 var email03 = LocationSheet.getRange（5,5）.getValue（）; 
 var email04 = LocationSheet.getRange（6,5）.getValue（）; 
 var email05 = LocationSheet.getRange（7,5）.getValue（）; 
 var email06 = LocationSheet.getRange（8,5）.getValue（）; 
 var email07 = LocationSheet.getRange（9,5）.getValue（）; 
 var email08 = LocationSheet.getRange（10,5）.getValue（）; 
 var email09 = LocationSheet.getRange（11,5）.getValue（）; 
 var email10 = LocationSheet.getRange（12,5）.getValue（）; 
 var email11 = LocationSheet.getRange（13,5）.getValue（）; 
 var email12 = LocationSheet.getRange（14,5）.getValue（）; 
 var email13 = LocationSheet.getRange（15,5）.getValue（）; 
 var email14 = LocationSheet.getRange（16,5）.getValue（）; 
 var email15 = LocationSheet.getRange（17,5）.getValue（）; 
 var email16 = LocationSheet.getRange（18,5）.getValue（）; 
 var email17 = LocationSheet.getRange（19,5）.getValue（）; 
 var email18 = LocationSheet.getRange（20,5）.getValue（）; 
 var email19 = LocationSheet.getRange（21,5）.getValue（）; 
 var email20 = LocationSheet.getRange（22,5）.getValue（）; 
 //声明正确的Depots以检查
 var LocationSheet = SpreadsheetApp.openById（itemSpreadsheetKey）.getSheetByName（LocationCodes）; 
 var depot01 = LocationSheet.getRange（3,4）.getValue（）; 
 var depot02 = LocationSheet.getRange（4,4）.getValue（）; 
 var depot03 = LocationSheet.getRange（5,4）.getValue（）; 
 var depot04 = LocationSheet.getRange（6,4）.getValue（）; 
 var depot05 = LocationSheet.getRange（7,4）.getValue（）; 
 var depot06 = LocationSheet.getRange（8,4）.getValue（）; 
 var depot07 = LocationSheet.getRange（9,4）.getValue（）; 
 var depot08 = LocationSheet.getRange（10,4）.getValue（）; 
 var depot09 = LocationSheet.getRange（11,4）.getValue（）; 
 var depot10 = LocationSheet.getRange（12,4）.getValue（）; 
 var depot11 = LocationSheet.getRange（13,4）.getValue（）; 
 var depot12 = LocationSheet.getRange（14,4）.getValue（）; 
 var depot13 = LocationSheet.getRange（15,4）.getValue（）; 
 var depot14 = LocationSheet.getRange（16,4）.getValue（）; 
 var depot15 = LocationSheet.getRange（17,4）.getValue（）; 
 var depot16 = LocationSheet.getRange（18,4）.getValue（）; 
 var depot17 = LocationSheet.getRange（19,4）.getValue（）; 
 var depot18 = LocationSheet.getRange（20,4）.getValue（）; 
 var depot19 = LocationSheet.getRange（21,4）.getValue（）; 
 var depot20 = LocationSheet.getRange（22,4）.getValue（）; （源===08  - 维护请求系统）{
 
 $ b //如果源被记录为'08  - 维护请求系统'，收件人为维护部分
 b var recipient = email00; 
 //或depot列出
} else if（location === depot01）{
 var recipient = email01; 
 
} else if（location === depot02）{
 var recipient = email02; 
 
} else if（location === depot03）{
 var recipient = email03; 
 
} else if（location === depot04）{
 var recipient = email04; 
 
} else if（location === depot05）{
 var recipient = email05; 
 
} else if（location === depot06）{
 var recipient = email06; 
 
} else if（location === depot07）{
 var recipient = email07; 
 
} else if（location === depot08）{
 var recipient = email08; 
 
} else if（location === depot09）{
 var recipient = email09; 
 
} else if（location === depot10）{
 var recipient = email10; 
 
} else if（location === depot11）{
 var recipient = email11; 
 
} else if（location === depot12）{
 var recipient = email12; 
 
} else if（location === depot13）{
 var recipient = email13; 
 
} else if（location === depot14）{
 var recipient = email14; 
 
} else if（location === depot15）{
 var recipient = email15; 
 
} else if（location === depot16）{
 var recipient = email16; 
 
} else if（location === depot17）{
 var recipient = email17; 
 
} else if（location === depot18）{
 var recipient = email18; 
 
} else if（location === depot19）{
 var recipient = email19; 
 
} else if（location === depot20）{
 var recipient = email20; 
 
} else {
 //并发送一封电子邮件给错误如果没有提供代码，则全部收到
 var recipient = email00; //根据需要更改
} 
 
 var sheet = SpreadsheetApp.openById（itemSpreadsheetKey）.getSheetByName（LOG）; 
 var lastRow = sheet.getLastRow（）; 
 var lrp1 = lastRow + 1 
 //将[getRange（lastRow + 1，1，1，**）]整数修改为reflet正在写入的页眉数量（如果添加了更多）
 var targetRange = sheet.getRange（lastRow + 1,1,1,12）.setValues（[[timeStamp，date，source，location，reporter，user，hazard，details，description，priority，recipient，actiondate]]）; 
 var Body ='在['+ location +']记录了一个新的['+ source +']日志条目，列为['+ hazard +']，请访问> http://goo.gl/LRjPI9<了解更多详情。此表单由['+ user +']提交，并带有时间戳['+ timeStamp +']。'
 
} 
 
 
 
函数addMemberRow（应用程序）//函数添加新行以启用多个表单条目
 {
 var table = app.getElementById（'table'）; 
 var tag = parseInt（table.getTag（））; 
 var numRows = tag + 1; 
 if（numRows> 1）{
 //行是相同的，因为最后一行退回一行。 
 table.removeCell（numRows-1，8）; 
 table.removeCell（numRows-1，8）; 
} 
 //创建日期选择框
 var DateBox = app.createDateBox（）。setWidth（'50px'）.setId（'DateBox'+ numRows）.setName（'DateBox'+ numRows行）.setFormat（UiApp.DateTimeFormat.DATE_SHORT）; 
 //创建'locations'下拉选择列表
 var LocationListBox = app.createListBox（）。setWidth（'100px'）。setId（'LocationListBox'+ numRows）.setName（'LocationListBox'+ numRows行）; 
 LocationListBox.addItem（''）; 
 LocationListBox.addItem（'BH  -  COMPANY（H& S Projects Team）'）; 
 LocationListBox.addItem（'CS  -  Conway Street Depot'）; 
 LocationListBox.addItem（'CT  -  Crawley Travel Office'）; 
 LocationListBox.addItem（'CY  -  Crawley Depot'）; 
 LocationListBox.addItem（'DU  -  Durrington Outstation'）; 
 LocationListBox.addItem（'EB  -  Eastbourne Outstation'）; 
 LocationListBox.addItem（'HS  - 霍舍姆船员区'）; 
 LocationListBox.addItem（'LR  -  Lewes Road Depot'）; 
 LocationListBox.addItem（'NE  -  Newhaven Engineering'）; 
 LocationListBox.addItem（'NO  -  Newhaven Outstation'）; 
 LocationListBox.addItem（'OS  -  One Stop Travel  -  North Street'）; 
 LocationListBox.addItem（'RH  -  Redhill Bus Station'）; 
 LocationListBox.addItem（'UK  -  Uckfield Outstation'）; 
 LocationListBox.addItem（'VP  -  Vehicles Procurement（Budget）'）; 
 LocationListBox.addItem（'WK  -  Whitehawk Depot'）; 
 //使用下拉选项创建'Source Code'列表
 var SourceListBox = app.createListBox（）。setWidth（'100px'）。setId（'SourceListBox'+ numRows）.setName（'SourceListBox' + numRows行）; 
 SourceListBox.addItem（''）; 
 SourceListBox.addItem（'01  - 事故/事故调查和报告'）; 
 SourceListBox.addItem（'02  - 公司或地点H& S委员会/论坛行动'）; 
 SourceListBox.addItem（'03  -  Emergency Preparedness reviews / activities'）; 
 SourceListBox.addItem（'04  - 员工危害/险情报告系统'）; 
 SourceListBox.addItem（'05  -  Fire Brigade visit actions'）; 
 SourceListBox.addItem（'06  - 集团H& S审计行动'）; 
 SourceListBox.addItem（'07-内部通用&特殊位置监视进程'）; 
 SourceListBox.addItem（'08  - 维护请求系统'）; 
 SourceListBox.addItem（'09  - 管理/员工会议/工具箱会谈和问题'）; 
 SourceListBox.addItem（'10  - 计划的预防性维护活动'）; 
 SourceListBox.addItem（'11  - 监管机构访问行动'）; 
 SourceListBox.addItem（'12  - 法定及其他特定检查/测试行为'）; 
 SourceListBox.addItem（'Other'）; 
 //创建输入'Reporters'name的文本框
 var ReporterTextBox = app.createTextBox（）。setWidth（'100px'）.setId（'ReportertTextBox'+ numRows）.setName（'ReporterTextBox'+ numRows行）; 
 //使用下拉选项创建'Priority Code'列表
 var PriorityListBox = app.createListBox（）。setWidth（'100px'）。setId（'PriorityListBox'+ numRows）.setName（'PriorityListBox' + numRows行）; 
 PriorityListBox.addItem（''）; 
 PriorityListBox.addItem（'02  -  WITHIN 24-48 HOURS'）; 
 PriorityListBox.addItem（'03  -  WITHIN 1 WEEK'）; 
 PriorityListBox.addItem（'04  -  WITHIN 1 MONTH'）; 
 PriorityListBox.addItem（'05  -  WITHIN 3 MONTHS'）; 
 PriorityListBox.addItem（'06  -  FOR MANAGEMENT DISCUSSION'）; 
 //用下拉选择创建'Hazard Box'列表
 var HazardListBox = app.createListBox（）。setWidth（'100px'）。setId（'HazardListBox'+ numRows）.setName（'HazardListBox' + numRows行）; 
 HazardListBox.addItem（''）; 
 HazardListBox.addItem（'01  - 接入设备故障'）; 
 HazardListBox.addItem（'02  -  Assault  -  verbal / physical'）; 
 HazardListBox.addItem（'03  - 阻止/保持开放的火线/出口'）; 
 HazardListBox.addItem（'04  - 承包商/访客不兼容或差/不安全的做法'）; 
 HazardListBox.addItem（'05  - 火灾/紧急情况相关'）; 
 HazardListBox.addItem（'06  - 急救条款'）; 
 HazardListBox.addItem（'07  - 叉车操作'）; 
 HazardListBox.addItem（'08  - 管家和泄漏'）; 
 HazardListBox.addItem（'09  - 起重设备'）; 
 HazardListBox.addItem（'10  - 照明故障'）; 
 HazardListBox.addItem（'11  -  Manual Handling'）; 
 HazardListBox.addItem（'12  - 缺少/不清楚的信息/警告/指示牌'）; 
 HazardListBox.addItem（'13  - 职业健康相关项目'）; 
 HazardListBox.addItem（'14  - 坑安全设备/障碍丢失/地方不好'）; 
 HazardListBox.addItem（'15  - 道路交通事故'）; 
 HazardListBox.addItem（'16  - 滑动，跳闸和跌落'）; 
 HazardListBox.addItem（'17  - 员工违规（例如PPE）或不良/不安全的做法'）; 
 HazardListBox.addItem（'18  - 洗手间/餐厅设施/家务'）; 
 HazardListBox.addItem（'19  - 工作设备故障'）; 
 HazardListBox.addItem（'20  -  Workplace transport movement'）; 
 //创建'Details Box'用于记录危险/缺陷
 var DetailsTextBox = app.createTextArea（）.setWidth（'150px'）.setId（'DetailsTextBox'+ numRows）.setName（'DetailsTextBox + numRows行）; 
 //创建'Description Box'给出更多细节
 var DescriptionTextBox = app.createTextArea（）。setWidth（'150px'）.setId（'DescriptionTextBox'+ numRows）.setName（'DescriptionTextBox'+ numRows行）; 
 //添加到页面
 table.setWidget（numRows，0，DateBox）
 .setWidget（numRows，1，LocationListBox）
 .setWidget（numRows，2，ReporterTextBox）
 .setWidget（numRows，3，SourceListBox）
 .setWidget（numRows，4，HazardListBox）
 .setWidget（numRows，5，DetailsTextBox）
 .setWidget（numRows，6，DescriptionTextBox ）
 .setWidget（numRows，7，PriorityListBox）
 table.setTag（numRows.toString（））; 
 addButtons（app）; 
 
 
 $ b函数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，8，addRowBtn）; 
 addRowBtn.addMouseUpHandler（addRemoveRowHandler）; 
 //删除行按钮和处理程序
 var removeRowBtn = app.createButton（' - '）。setId（'removeOne'）。setTitle（'Remove row'）; 
 table.setWidget（numRows，9，removeRowBtn）; 
 removeRowBtn.addMouseUpHandler（addRemoveRowHandler）; 
 
 
 $ b函数_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（））; 
 addMemberRow（app）; 
} 
 if（source =='removeOne'）{
 if（tag> 1）{
 //将标签减一个
 var numRows =标签-1; 
 table.removeRow（tag）; 
 //设置表格的新标签
 table.setTag（numRows.toString（））; 
 //在前一行添加按钮
 addButtons（app）; 
} 
} 
返回应用; 
} 
  




解决方案

电子表格有两张纸，一个被命名为LocationCodes。该代码引用了该表：



  var LocationSheet = SpreadsheetApp.openById（itemSpreadsheetKey）.getSheetByName（LocationCodes）; 
  

并创建 LocationSheet 变量作为全局变量。





另外还有一个名为LOG的表。这就是输入表单中的所有数据都要去的地方。





您希望代码查看LOG表单中的每一行，并查看LOG表单中的每一行存在，发送电子邮件。电子邮件从某处被抬起头来。显而易见的选择是LOG表中F列中的电子邮件地址，但我需要确切地知道，因为K列中有另一个电子邮件地址。





在代码中需要一个循环，它只循环需要在LOG工作表中检查的行数。





代码从表中逐行获取每封邮件：

  var email00 = LocationSheet.getRange（33,5）.getValue（）
 //后跟其他电子邮件，因为它们出现
 var email01 = LocationSheet.getRange（3,5）.getValue（）; 
 var email02 = LocationSheet.getRange（4,5）.getValue（）; 
 var email03 = LocationSheet.getRange（5,5）.getValue（）; 
 var email04 = LocationSheet.getRange（6,5）.getValue（）; 
 var email05 = LocationSheet.getRange（7,5）.getValue（）; 
 var email06 = LocationSheet.getRange（8,5）.getValue（）; 
  

上面的代码获取单个单元格值。如果您想一次发送所有这些电子邮件，那就需要不同的代码。



您需要通过编程循环遍历每封电子邮件。这将用于为每个电子邮件地址发送一封单独​​的电子邮件。如果您想发送一封电子邮件到很多电子邮件地址，那么您需要将它们连接起来。





代码中没有循环，或任何会发送多封电子邮件。




Ok, progress is being made: I have managed to get the form entries to be written to the spreadsheet and it is working except the last entry is being entered twice. Any ideas what I am doing wrong anyone?

The amended code is:

function InsertDataInSheet(e)  //Function to insert data into spreadsheet on clicking submit
{

var app = UiApp.getActiveApplication();
  
//get number of rows to input
  var num = parseInt(e.parameter.table_tag);  
  var num = num+1;
  
//set increment step through  
  for (var i = 1; i < num ; i++ ) {
  
//Declare varialbe fields to collect data from
    var user         = Session.getActiveUser().getEmail();
    var date         = e.parameter['DateBox'+i];
    var location     = e.parameter['LocationListBox'+i];
    var source       = e.parameter['SourceListBox'+i];
    var reporter     = e.parameter['ReporterTextBox'+i];
    var priority     = e.parameter['PriorityListBox'+i];
    var hazard       = e.parameter['HazardListBox'+i];
    var details      = e.parameter['DetailsTextBox'+i];
    var description  = e.parameter['DescriptionTextBox'+i];
    var timeStamp    = new Date();
        
    //Decide date that this needs to be closed by
    if (priority === '02 - WITHIN 24-48 HOURS') {
      var dateTemp = new Date(date);
      dateTemp.setDate(dateTemp.getDate()+2);
      var actiondate = dateTemp; 
    }
    if (priority === '03 - WITHIN 1 WEEK') {
        var dateTemp = new Date(date);
      dateTemp.setDate(dateTemp.getDate()+7);
      var actiondate = dateTemp;  
    } 
     if (priority === '04 - WITHIN 1 MONTH') { 
        var dateTemp = new Date(date);
      dateTemp.setDate(dateTemp.getDate()+31);
      var actiondate = dateTemp;  
    }  
     if (priority === '05 - WITHIN 3 MONTHS') { 
        var dateTemp = new Date(date);
      dateTemp.setDate(dateTemp.getDate()+90);
      var actiondate = dateTemp;  
    } 
     if (priority === '06 - FOR MANAGEMENT DISCUSSION') { 
      var dateTemp = new Date(date);
      dateTemp.setDate(dateTemp.getDate()+365);
      var actiondate = dateTemp;  
    }
    
      //establish email addresses
  //Declare correct range to obtain values
    var LocationSheet = SpreadsheetApp.openById(itemSpreadsheetKey).getSheetByName("LocationCodes")
  //Start with central maintenance department
    var email00 = LocationSheet.getRange(33,5).getValue()
  //followed by other emails as they appear
    var email01 = LocationSheet.getRange(3,5).getValue();
    var email02 = LocationSheet.getRange(4,5).getValue();
    var email03 = LocationSheet.getRange(5,5).getValue();
    var email04 = LocationSheet.getRange(6,5).getValue();
    var email05 = LocationSheet.getRange(7,5).getValue();
    var email06 = LocationSheet.getRange(8,5).getValue();
    var email07 = LocationSheet.getRange(9,5).getValue();
    var email08 = LocationSheet.getRange(10,5).getValue();
    var email09 = LocationSheet.getRange(11,5).getValue();
    var email10 = LocationSheet.getRange(12,5).getValue();
    var email11 = LocationSheet.getRange(13,5).getValue();
    var email12 = LocationSheet.getRange(14,5).getValue();
    var email13 = LocationSheet.getRange(15,5).getValue();
    var email14 = LocationSheet.getRange(16,5).getValue();
    var email15 = LocationSheet.getRange(17,5).getValue();
    var email16 = LocationSheet.getRange(18,5).getValue();
    var email17 = LocationSheet.getRange(19,5).getValue();
    var email18 = LocationSheet.getRange(20,5).getValue();
    var email19 = LocationSheet.getRange(21,5).getValue();
    var email20 = LocationSheet.getRange(22,5).getValue();
  //declare the correct Depots to check
    var LocationSheet = SpreadsheetApp.openById(itemSpreadsheetKey).getSheetByName("LocationCodes");
    var depot01 = LocationSheet.getRange(3,4).getValue();
    var depot02 = LocationSheet.getRange(4,4).getValue();
    var depot03 = LocationSheet.getRange(5,4).getValue();
    var depot04 = LocationSheet.getRange(6,4).getValue();
    var depot05 = LocationSheet.getRange(7,4).getValue();
    var depot06 = LocationSheet.getRange(8,4).getValue();
    var depot07 = LocationSheet.getRange(9,4).getValue();
    var depot08 = LocationSheet.getRange(10,4).getValue();
    var depot09 = LocationSheet.getRange(11,4).getValue();
    var depot10 = LocationSheet.getRange(12,4).getValue();
    var depot11 = LocationSheet.getRange(13,4).getValue();
    var depot12 = LocationSheet.getRange(14,4).getValue();
    var depot13 = LocationSheet.getRange(15,4).getValue();
    var depot14 = LocationSheet.getRange(16,4).getValue();
    var depot15 = LocationSheet.getRange(17,4).getValue();
    var depot16 = LocationSheet.getRange(18,4).getValue();
    var depot17 = LocationSheet.getRange(19,4).getValue();
    var depot18 = LocationSheet.getRange(20,4).getValue();
    var depot19 = LocationSheet.getRange(21,4).getValue();
    var depot20 = LocationSheet.getRange(22,4).getValue();

  //if source is recorded as '08 - Maitenance Request System', the recipient is maintenance deparment
    if (source === "08 - Maintenance Request System"){
    var recipient = email00;
  //or depots as listed
    } else if(location === depot01){
    var recipient = email01;
  
    } else if(location === depot02){
    var recipient = email02;
  
    } else if(location === depot03){
    var recipient = email03;
  
    } else if(location === depot04){
    var recipient = email04;
  
    } else if(location === depot05){
    var recipient = email05;
  
    } else if(location === depot06){
    var recipient = email06;
  
    } else if(location === depot07){
    var recipient = email07;
  
    } else if(location === depot08){
    var recipient = email08;
  
    } else if(location === depot09){
    var recipient = email09;
  
    } else if(location === depot10){
    var recipient = email10;
  
    } else if(location === depot11){
    var recipient = email11;
  
    } else if(location === depot12){
    var recipient = email12;
  
    } else if(location === depot13){
    var recipient = email13;
  
    } else if(location === depot14){
    var recipient = email14;
  
    } else if(location === depot15){
    var recipient = email15;
  
    } else if(location === depot16){
    var recipient = email16;
  
    } else if(location === depot17){
    var recipient = email17;
  
    } else if(location === depot18){
    var recipient = email18;
  
    } else if(location === depot19){
    var recipient = email19;
  
    } else if(location === depot20){
    var recipient = email20;
  
    } else {
  //and send an email to the error catch all if no code supplied
    var recipient = email00; //change as necessary  
    }

    var sheet = SpreadsheetApp.openById(itemSpreadsheetKey).getSheetByName('LOG');
    var lastRow = sheet.getLastRow();
    var lrp1 = lastRow+1
    var targetRange = sheet.getRange(lastRow+1, 1, 1, 12).setValues([[timeStamp,date,source,location,reporter,user,hazard,details,description,priority,recipient,actiondate]]);
 
  } 
    
    var sheet = SpreadsheetApp.openById(itemSpreadsheetKey).getSheetByName("LOG");
    var lastRow = sheet.getLastRow();
    var lrp1 = lastRow+1
  //Amend [getRange(lastRow+1, 1, 1, **)] integer to reflet number of headers being written if more added
    var targetRange = sheet.getRange(lastRow+1, 1, 1, 12).setValues([[timeStamp,date,source,location,reporter,user,hazard,details,description,priority,recipient,actiondate]]);
    var Body = 'A new [' +source+ '] log entry has been recorded at [' +location+ '], listed as [' + hazard+ ']. This form was submitted by [' +user+ '] with the timestamp [' +timeStamp+ '].'

    }
 

I have not got any further, so I am breaking the question in to two. This is part 1. My code is still failing and it is because (if I understand Sandy correctly (thanks again btw)) there is no loop.

The code writes what I need in the right order I need it, it just doesn't write a new row for each entry - I only get the first row written.

Any help greatly appreciated.

The pseudocode reads like this:

1. Create a form for filling out a form embedded in a website.

2. Create + & - buttons for adding or removing rows as required.

3. If + clicked add row.

4. If - clicked remove row.

5. When submit clicked, open the spreadsheet and write a new row of data for every row created in the form.

6. Once submitted create a notification page.

The code I have so far is:

    //spreadsheet key is needed to access the correct spreadsheet. It is located AFTER "/d/" and before "/edit" in the URL
var itemSpreadsheetKey = '1mgp5d6gZydrn-bI6KfCNNOsIj9sh0dLJMzVq4Al4ypY';
var LogSheet = SpreadsheetApp.openById(itemSpreadsheetKey).getSheetByName("Log");
var LocationSheet = SpreadsheetApp.openById(itemSpreadsheetKey).getSheetByName("LocationCodes");
var HazardSheet = SpreadsheetApp.openById(itemSpreadsheetKey).getSheetByName("HazardCodes");
var SourceSheet = SpreadsheetApp.openById(itemSpreadsheetKey).getSheetByName("SourceCodes");


function doGet() //the 'doGet' function retrieves the code from the embedded URL in the gadget
  {
      var app = UiApp.createApplication();
      var panel = app.createVerticalPanel();
      var table = app.createFlexTable().setId('table').setTag('0').setBorderWidth(0);

  //Write the header for the table
      var headerArray = ['DATE' , 'DEPOT/SITE' , 'REPORTER', 'SOURCE CODE', 'HAZARD CODE' , 'BRIEF DETAILS' , 'FULL DESCRIPTION' , 'PRIORITY'];
      for(var i=0; i<headerArray.length; i++)
      {
      table.setWidget(0, i, app.createLabel(headerArray[i]));
      }

  //Add the first row of form elements to input Member information
      addMemberRow(app);

  //Add a button to submit the info
      var button = app.createSubmitButton('CLICK TO SUBMIT');

  // call the InsertDataInSheet routine
      var handler = app.createServerHandler('InsertDataInSheet');
      handler.addCallbackElement(panel);
      button.addMouseUpHandler(handler);
      panel.add(table)
      .add(button);
      app.add(panel);
      return app;
  }


function InsertDataInSheet(e) //Function to insert data in the sheet on clicking the submit button
  {
    var app = UiApp.getActiveApplication();
  //get input values from last row of data
    var num = parseInt(e.parameter.table_tag);  
    var num = num+1;
  //Set increment value to step through
    for (var i = 1; i < num ; i++ )
    {    
  //Declare variable fields to collect data from
    var user            = Session.getActiveUser().getEmail();
    var date            = e.parameter['DateBox'+i];
    var location        = e.parameter['LocationListBox'+i];
    var source          = e.parameter['SourceListBox'+i];         
    var reporter        = e.parameter['ReporterTextBox'+i];
    var priority        = e.parameter['PriorityListBox'+i];
    var hazard          = e.parameter['HazardListBox'+i];
    var details         = e.parameter['DetailsTextBox'+i];
    var description     = e.parameter['DescriptionTextBox'+i];
    var user            = Session.getUser().getUserLoginId();
    var timeStamp       = new Date();

  //Decide date that this needs to be closed by
    if (priority === '02 - WITHIN 24-48 HOURS')
    { 
    var dateTemp = new Date(date);
    dateTemp.setDate(dateTemp.getDate()+2);
    var actiondate = dateTemp; 
    }
    if (priority == '03 - WITHIN 1 WEEK')
    {
    var dateTemp = new Date(date);
    dateTemp.setDate(dateTemp.getDate()+7);
    var actiondate = dateTemp;  
    }
    if (priority == '04 - WITHIN 1 MONTH')
    {
    var dateTemp = new Date(date);
    dateTemp.setDate(dateTemp.getDate()+31);
    var actiondate = dateTemp;  
    }
    if (priority == '05 - WITHIN 3 MONTHS')
    {
    var dateTemp = new Date(date);
    dateTemp.setDate(dateTemp.getDate()+90);
    var actiondate = dateTemp;  
    }
    if (priority == '06 - FOR MANAGEMENT DISCUSSION')
    {
    var actiondate = "N/A";  
    }

    app = UiApp.getActiveApplication().remove(0);
    app.createVerticalPanel()
    .setId('info')
    .setVisible(true)      
    .setStyleAttribute('left', 0)  
    .setStyleAttribute('top', 0)        
    .setStyleAttribute('zIndex', '1')
    .setStyleAttribute('position', 'fixed')
    .setStyleAttribute('background', 'white')
    .setHeight('400px')
    .setStyleAttribute('text-align', 'center')
    .setBorderWidth(0)
    .setWidth('500px');
    app.add(app.createLabel(''));
    app.add(app.createLabel('Thank you. Your form has been submitted to the Proactive/Reactive Hazard Logging database and ' +recipient+ ' (the Health & Safety Site Coordinator) has been alerted.'));
    app.add(app.createLabel('A copy of this email has been sent to ' +user+ ' with the timestamp ' +timeStamp+ ' which should be quoted if follow up is required.'));
    app.add(app.createLabel('Please refresh this page to add submit another form, or close the page to exit'));
    return app.close();
    }
  //establish email addresses
  //Declare correct range to obtain values
    var LocationSheet = SpreadsheetApp.openById(itemSpreadsheetKey).getSheetByName("LocationCodes")
  //Start with central maintenance department
    var email00 = LocationSheet.getRange(33,5).getValue()
  //followed by other emails as they appear
    var email01 = LocationSheet.getRange(3,5).getValue();
    var email02 = LocationSheet.getRange(4,5).getValue();
    var email03 = LocationSheet.getRange(5,5).getValue();
    var email04 = LocationSheet.getRange(6,5).getValue();
    var email05 = LocationSheet.getRange(7,5).getValue();
    var email06 = LocationSheet.getRange(8,5).getValue();
    var email07 = LocationSheet.getRange(9,5).getValue();
    var email08 = LocationSheet.getRange(10,5).getValue();
    var email09 = LocationSheet.getRange(11,5).getValue();
    var email10 = LocationSheet.getRange(12,5).getValue();
    var email11 = LocationSheet.getRange(13,5).getValue();
    var email12 = LocationSheet.getRange(14,5).getValue();
    var email13 = LocationSheet.getRange(15,5).getValue();
    var email14 = LocationSheet.getRange(16,5).getValue();
    var email15 = LocationSheet.getRange(17,5).getValue();
    var email16 = LocationSheet.getRange(18,5).getValue();
    var email17 = LocationSheet.getRange(19,5).getValue();
    var email18 = LocationSheet.getRange(20,5).getValue();
    var email19 = LocationSheet.getRange(21,5).getValue();
    var email20 = LocationSheet.getRange(22,5).getValue();
  //declare the correct Depots to check
    var LocationSheet = SpreadsheetApp.openById(itemSpreadsheetKey).getSheetByName("LocationCodes");
    var depot01 = LocationSheet.getRange(3,4).getValue();
    var depot02 = LocationSheet.getRange(4,4).getValue();
    var depot03 = LocationSheet.getRange(5,4).getValue();
    var depot04 = LocationSheet.getRange(6,4).getValue();
    var depot05 = LocationSheet.getRange(7,4).getValue();
    var depot06 = LocationSheet.getRange(8,4).getValue();
    var depot07 = LocationSheet.getRange(9,4).getValue();
    var depot08 = LocationSheet.getRange(10,4).getValue();
    var depot09 = LocationSheet.getRange(11,4).getValue();
    var depot10 = LocationSheet.getRange(12,4).getValue();
    var depot11 = LocationSheet.getRange(13,4).getValue();
    var depot12 = LocationSheet.getRange(14,4).getValue();
    var depot13 = LocationSheet.getRange(15,4).getValue();
    var depot14 = LocationSheet.getRange(16,4).getValue();
    var depot15 = LocationSheet.getRange(17,4).getValue();
    var depot16 = LocationSheet.getRange(18,4).getValue();
    var depot17 = LocationSheet.getRange(19,4).getValue();
    var depot18 = LocationSheet.getRange(20,4).getValue();
    var depot19 = LocationSheet.getRange(21,4).getValue();
    var depot20 = LocationSheet.getRange(22,4).getValue();

  //if source is recorded as '08 - Maitenance Request System', the recipient is maintenance deparment
    if (source === "08 - Maintenance Request System"){
    var recipient = email00;
  //or depots as listed
    } else if(location === depot01){
    var recipient = email01;

    } else if(location === depot02){
    var recipient = email02;

    } else if(location === depot03){
    var recipient = email03;

    } else if(location === depot04){
    var recipient = email04;

    } else if(location === depot05){
    var recipient = email05;

    } else if(location === depot06){
    var recipient = email06;

    } else if(location === depot07){
    var recipient = email07;

    } else if(location === depot08){
    var recipient = email08;

    } else if(location === depot09){
    var recipient = email09;

    } else if(location === depot10){
    var recipient = email10;

    } else if(location === depot11){
    var recipient = email11;

    } else if(location === depot12){
    var recipient = email12;

    } else if(location === depot13){
    var recipient = email13;

    } else if(location === depot14){
    var recipient = email14;

    } else if(location === depot15){
    var recipient = email15;

    } else if(location === depot16){
    var recipient = email16;

    } else if(location === depot17){
    var recipient = email17;

    } else if(location === depot18){
    var recipient = email18;

    } else if(location === depot19){
    var recipient = email19;

    } else if(location === depot20){
    var recipient = email20;

    } else {
  //and send an email to the error catch all if no code supplied
    var recipient = email00; //change as necessary  
    }

    var sheet = SpreadsheetApp.openById(itemSpreadsheetKey).getSheetByName("LOG");
    var lastRow = sheet.getLastRow();
    var lrp1 = lastRow+1
  //Amend [getRange(lastRow+1, 1, 1, **)] integer to reflet number of headers being written if more added
    var targetRange = sheet.getRange(lastRow+1, 1, 1, 12).setValues([[timeStamp,date,source,location,reporter,user,hazard,details,description,priority,recipient,actiondate]]);
    var Body = 'A new [' +source+ '] log entry has been recorded at [' +location+ '], listed as [' + hazard+ '], please visit > http://goo.gl/LRjPI9 < for further details. This form was submitted by [' +user+ '] with the timestamp [' +timeStamp+ '].'

    }



function addMemberRow(app) //Function adds new rows to enable multiple form entries
  {
    var table = app.getElementById('table');
    var tag = parseInt(table.getTag());
    var numRows = tag+1;
    if(numRows >1){
  //Row is same, as last row drops back one.
    table.removeCell(numRows-1, 8);
    table.removeCell(numRows-1, 8);
    }
  //Create date picker box
    var DateBox = app.createDateBox().setWidth('50px').setId('DateBox'+numRows).setName('DateBox'+numRows).setFormat(UiApp.DateTimeFormat.DATE_SHORT);
  // Create 'locations' List with drop down selections
    var LocationListBox = app.createListBox().setWidth('100px').setId('LocationListBox'+numRows).setName('LocationListBox'+numRows);
    LocationListBox.addItem('');
    LocationListBox.addItem('BH - COMPANY (H&S Projects Team)');
    LocationListBox.addItem('CS - Conway Street Depot');
    LocationListBox.addItem('CT - Crawley Travel Office');
    LocationListBox.addItem('CY - Crawley Depot');
    LocationListBox.addItem('DU - Durrington Outstation');
    LocationListBox.addItem('EB - Eastbourne Outstation');
    LocationListBox.addItem('HS - Horsham Crew Area');
    LocationListBox.addItem('LR - Lewes Road Depot');
    LocationListBox.addItem('NE - Newhaven Engineering');
    LocationListBox.addItem('NO - Newhaven Outstation');
    LocationListBox.addItem('OS - One Stop Travel - North Street');
    LocationListBox.addItem('RH - Redhill Bus Station');
    LocationListBox.addItem('UK - Uckfield Outstation');
    LocationListBox.addItem('VP - Vehicles Procurement (Budget)');
    LocationListBox.addItem('WK - Whitehawk Depot');  
  // Create 'Source Code' list with drop down selections
    var SourceListBox = app.createListBox().setWidth('100px').setId('SourceListBox'+numRows).setName('SourceListBox'+numRows);
    SourceListBox.addItem('');
    SourceListBox.addItem('01 - Accident/Incident investigations & reports');
    SourceListBox.addItem('02 - Company or location H&S Committee/Forum actions');
    SourceListBox.addItem('03 - Emergency Preparedness reviews/activities');
    SourceListBox.addItem('04 - Employee Hazard / Near Miss Reporting System');
    SourceListBox.addItem('05 - Fire Brigade visit actions');
    SourceListBox.addItem('06 - Group H&S audit actions');
    SourceListBox.addItem('07 - Internal general & speicific location monitoring processes');
    SourceListBox.addItem('08 - Maintenance Request System');
    SourceListBox.addItem('09 - Management/employee meetings/toolbox talks & questions');
    SourceListBox.addItem('10 - Planned Preventative Maintenance activities');
    SourceListBox.addItem('11 - Regulatory bodies visit actions');
    SourceListBox.addItem('12 - Statutory & other specific inspection/testing actions');
    SourceListBox.addItem('Other');    
  // Create text box for entering 'Reporters' name
    var ReporterTextBox = app.createTextBox().setWidth('100px').setId('ReportertTextBox'+numRows).setName('ReporterTextBox'+numRows);
  // Create 'Priority Code' list with drop down selections
    var PriorityListBox = app.createListBox().setWidth('100px').setId('PriorityListBox'+numRows).setName('PriorityListBox'+numRows);
    PriorityListBox.addItem('');
    PriorityListBox.addItem('02 - WITHIN 24-48 HOURS');
    PriorityListBox.addItem('03 - WITHIN 1 WEEK');
     PriorityListBox.addItem('04 - WITHIN 1 MONTH');
    PriorityListBox.addItem('05 - WITHIN 3 MONTHS');
    PriorityListBox.addItem('06 - FOR MANAGEMENT DISCUSSION');
  // Create 'Hazard Box' list with drop down selection
    var HazardListBox = app.createListBox().setWidth('100px').setId('HazardListBox'+numRows).setName('HazardListBox'+numRows);
    HazardListBox.addItem('');
    HazardListBox.addItem('01 - Access Equipment Fault');
    HazardListBox.addItem('02 - Assault - verbal/physical');
    HazardListBox.addItem('03 - Blocked/held open fire route/exit');
    HazardListBox.addItem('04 - Contractor/visitor non-compiance or poor/unsafe practice');
    HazardListBox.addItem('05 - Fire/emergency related');
    HazardListBox.addItem('06 - First Aid provision');
    HazardListBox.addItem('07 - Fork Lift Truck operation');
    HazardListBox.addItem('08 - Housekeeping & Spillages');
    HazardListBox.addItem('09 - Lifting Equipment');
    HazardListBox.addItem('10 - Lighting Fault');
    HazardListBox.addItem('11 - Manual Handling');
    HazardListBox.addItem('12 - Missing/unclear information/warning/directional signage');
    HazardListBox.addItem('13 - Occupational Health related item');
    HazardListBox.addItem('14 - Pit safety equipment/barriers missing/poorly in place');
    HazardListBox.addItem('15 - Road Traffic Accident');
    HazardListBox.addItem('16 - Slips, trips & falls');
    HazardListBox.addItem('17 - Staff non-compliance (e.g. PPE) or poor/unsafe practice');
    HazardListBox.addItem('18 - Washroom/mess rooms facilities/housekeeping ');
    HazardListBox.addItem('19 - Work equipment fault');
    HazardListBox.addItem('20 - Workplace transport movement');
  // Create 'Details Box' for recording the hazard/defect
    var DetailsTextBox = app.createTextArea().setWidth('150px').setId('DetailsTextBox'+numRows).setName('DetailsTextBox'+numRows);
  // Create 'Description Box' for giving further details
    var DescriptionTextBox = app.createTextArea().setWidth('150px').setId('DescriptionTextBox'+numRows).setName('DescriptionTextBox'+numRows);
  //Add to page
    table.setWidget(numRows, 0, DateBox)
       .setWidget(numRows, 1, LocationListBox)
       .setWidget(numRows, 2, ReporterTextBox)
       .setWidget(numRows, 3, SourceListBox)
       .setWidget(numRows, 4, HazardListBox)
       .setWidget(numRows, 5, DetailsTextBox)
       .setWidget(numRows, 6, DescriptionTextBox)
       .setWidget(numRows, 7, PriorityListBox)       
    table.setTag(numRows.toString());
    addButtons(app);
  }


function addButtons(app) //add or remove buttons as required
  {
    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, 8, addRowBtn);
    addRowBtn.addMouseUpHandler(addRemoveRowHandler);
  //remove row button and handler
    var removeRowBtn = app.createButton('-').setId('removeOne').setTitle('Remove row');
    table.setWidget(numRows, 9, removeRowBtn);
    removeRowBtn.addMouseUpHandler(addRemoveRowHandler);
  }


function _addRemoveRow(e) //add or remove rows as required
  {
    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());
    addMemberRow(app);
    }
    else if(source == 'removeOne'){
    if(tag > 1){ 
  //Decrement 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;
  }

解决方案

The spreadsheet has two sheets, one is named "LocationCodes". The code refers to that sheet with:

var LocationSheet = SpreadsheetApp.openById(itemSpreadsheetKey).getSheetByName("LocationCodes");

and creates the LocationSheet variable as a global variable.

There is also another sheet named "LOG". That's where all the data from the input form is going.

You want the code to look through every row in the LOG sheet, and for every row in the LOG sheet that exists, send an email. The email gets looked up from somewhere. The obvious choice would be the "Email Address" in column "F" of the LOG sheet, but I need to know absolutely for sure, because there is another email address in column K.

You need a loop in the code that only loops through the number of rows that you need checked in the LOG sheet. Will the number of rows in the LOG sheet always be the same?

The code is getting each individual email from the sheet, line by line:

    var email00 = LocationSheet.getRange(33,5).getValue()
  //followed by other emails as they appear
    var email01 = LocationSheet.getRange(3,5).getValue();
    var email02 = LocationSheet.getRange(4,5).getValue();
    var email03 = LocationSheet.getRange(5,5).getValue();
    var email04 = LocationSheet.getRange(6,5).getValue();
    var email05 = LocationSheet.getRange(7,5).getValue();
    var email06 = LocationSheet.getRange(8,5).getValue();

That code above, gets an individual cell value. If you want to email all of those emails at once, that requires different code.

You would need to loop through each email with a programming loop. That would be for sending a separate email for each email address. If you want to send one email to lots of email addresses, then you'd need to concatenate them.

There is no loop in the code, or anything that would send multiple emails.

这篇关于来自多个表单的多个电子表格行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！