将提交的表单响应复制到新工作表（PROJECTS）并希望能够编辑 [英] Copy submitted Form Responses to a New Sheet (PROJECTS) and wanting to be able to edit

问题描述

我需要帮助 - 这里有一点历史记录。

我有一个google日程安排 - https://docs.google.com/spreadsheet/ccc?key=0ArmKEmhue9OgdEFwMHBldFpGenNVdzJUQThCQU9Qcmc&usp=sharing

在这个时间表内有很多表单，但是我希望能得到帮助的表单是表单响应1和项目表

我有Google表单 - https://docs.google.com/forms/d/1yFy3i5H3abhFjdvchuJq2ARODcfRGo6KNkOAgeRIMMU/ view form

它与google日程安排相关联，并且每次提交数据都会显示在Form Responses 1表单中 -

有一个脚本应用于电子表格 -

COPYING TO / FROM SCRIPT

  / ** 
 *只要对电子表格进行
 *更改，就会调用名为onEdit的函数。 
 * 
 * @param {object} e编辑事件（本例中未使用）
 * / 
函数onFormSubmit（e）{
 var copyFromRange ='表格回应1！A2：AC999'; 
 var copyToRangeStart ='PROJECTS！A71：AC999'; 
 copyValuesOnly（copyFromRange，copyToRangeStart）; 
} 
 
 / ** 
 *此函数会将给定范围的值复制到
 *第二个范围，该范围从给定单元格引用$ b开始例如：
 * @param {string} copyToRangeStart单元格引用，例如：
 * / 
函数copyValuesOnly（copyFromRange，copyToRangeStart） {
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var source = ss.getRange（copyFromRange）; 
 source.copyTo（ss.getRange（copyToRangeStart），{contentsOnly：true}）; 
 
 $ / code> 

这样可以将数据复制到PROJECTS表中，以便多个用户可以编辑和更新BUT意识到每次新提交时都会覆盖数据



另外，只需在C列项目编号下的Form Responses 1表单中注明是一个forumula = ArrayFormula（MC& TEXT（ROW（A2：A）-1;000）），我放置了允许自动编号每个提交项目编号

我还有一个ARCHIVE脚本 -

如果您输入了一个项目， 完成然后移动到存档表

函数onEdit（）{

  //当在列中输入魔术值时，将一行从表单移动到另一行
 //根据需要调整以下变量
 //请参阅https：// productforums。 google.com/d/topic/docs/ehoCZjFPBao/discussion 
 var sheetNameToWatch1 =PROJECTS; 
 var sheetNameToWatch2 =ADVERTISING; 
 var columnNumberToWatch = 7; //列A = 1，B = 2等
 var valueToWatch =DONE; 
 var sheetNameToMoveTheRowTo =ARCHIVE; 
 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var range = sheet.getActiveCell（）; （（sheet.getName（）== sheetNameToWatch1 || sheet.getName（）== sheetNameToWatch2）&& amp;& range.getColumn（）== columnNumberToWatch&& amp; range.getValue（（））{
 
 ）== valueToWatch）{
 var targetSheet = ss.getSheetByName（sheetNameToMoveTheRowTo）; 
 var targetRange = targetSheet.getRange（targetSheet.getLastRow（）+ 1，1）; 
 sheet.getRange（range.getRow（），1，1，sheet.getLastColumn（））。moveTo（targetRange）; 
 sheet.deleteRow（range.getRow（））; 
} 
} 
  

问题 - - 当使用表单输入新的提交时，数据进入表单响应1表单，然后复制到PROJECTS表单，但不能编辑PROJECTS下的数据，因为每个提交都会覆盖任何编辑并引入原始表单提交数据 -

我希望发生的情况是数据出现在Form Responses 1表格中，然后在每次提交时复制到PROJECTS表格中，但是当新提交进来时，到PROJECTS下的数据保留在那里

问题 - 项目编号 - C列第2行下的Form Responses 1表格下的公式设置 - 每行有一个项目编号分配，然后复制到PROJECTS工作表，当该行然后归档（完成）并移动到ARCHIVE工作表时分配给该行的项目编号随之而来，但是当您提交一个新的项目相同的数字可以appl对一个新项目采用这种方式，那么最终当你存档所有项目时，许多项目都会有相同的编号，是否有办法让每个项目都有一个唯一的项目号？

* 问题 - 提交表单 - 我不确定为什么，但是当新的提交进来时，数据每次都进入113行，而不是在表单响应行的末尾？ p>

任何帮助都将不胜感激 - 我认为它会起作用，但似乎该脚本不适合我想要的功能

非常感谢，
Paola G

解决方案

给RiëlNotermans（Zzapps）

https://plus.google.com/u/0/116507112418725270540/posts/1i75t1wFEfh

 功能onFormSubmit（e）{
 
 
 var responses = SpreadsheetApp.getActiveSpreadsheet （）.getSheetByName（Form Responses 1）; 
 var projects = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（PROJECTS）; 
 var lastrow = respond.getLastRow（）; 
 var col = responses.getLastColumn（）; 
 var row = respond.getRange（lastrow，1，1，col）.getValues（）; 
 
 projects.appendRow（row [0]）; 
 // copyValuesOnly（copyFromRange，copyToRangeStart）; 
} 
  

确保两张纸下面都没有空行，有一些项目编号。只要确保最后一行是数据。

I need help please - here's a little history..

I have a google schedule - https://docs.google.com/spreadsheet/ccc?key=0ArmKEmhue9OgdEFwMHBldFpGenNVdzJUQThCQU9Qcmc&usp=sharing

Within that schedule there are numerous sheets but the ones that I was hoping to get help with is the Form Responses 1 and PROJECTS sheet

I have a google form - https://docs.google.com/forms/d/1yFy3i5H3abhFjdvchuJq2ARODcfRGo6KNkOAgeRIMMU/viewform

It's linked to the google schedule and with each submission the data goes in the Form Responses 1 sheet -

There is a script applied to the spreadsheet -

COPYING TO/FROM SCRIPT

/**
 * A function named onEdit will be called whenever
 * a change is made to the spreadsheet.
 * 
 * @param  {object} e The edit event (not used in this case)
 */
function onFormSubmit(e){
  var copyFromRange = 'Form Responses 1!A2:AC999';
  var copyToRangeStart = 'PROJECTS!A71:AC999';
  copyValuesOnly(copyFromRange, copyToRangeStart);
}

/**
 * This function will copy the values from a given range to
 * a second range, which starts from the given cell reference
 * 
 * @param  {string} copyFromRange    Range reference eg: 
 * @param  {string} copyToRangeStart Cell reference eg:
 */
function copyValuesOnly(copyFromRange, copyToRangeStart) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange(copyFromRange);
  source.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true});
}

This is allowing the data to copy over to the PROJECTS sheet so multiple users can edit and update BUT realized that it overrides the data each time a new submission comes in

Also just to note within the Form Responses 1 sheet under column C "Project Number" there is a forumula =ArrayFormula("MC"&TEXT(ROW(A2:A)-1 ; "000") ) that I placed to allow for automatically numbering each submission with a project number

I also have a ARCHIVE script -

Once a project is final, under column G "Archive" if you type "DONE" it then moves to the ARCHIVE sheet

function onEdit() {

  // moves a row from a sheet to another when a magic value is entered in a column
  // adjust the following variables to fit your needs
  // see https://productforums.google.com/d/topic/docs/ehoCZjFPBao/discussion
  var sheetNameToWatch1 = "PROJECTS";
  var sheetNameToWatch2 = "ADVERTISING";
  var columnNumberToWatch = 7; // column A = 1, B = 2, etc.
  var valueToWatch = "DONE";
  var sheetNameToMoveTheRowTo = "ARCHIVE";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();

  if ( (sheet.getName() == sheetNameToWatch1 || sheet.getName() == sheetNameToWatch2) && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(range.getRow());
  }
}

ISSUES -COPYING - When a new submission is entered using the form, the data comes in Form Responses 1 sheet and then copies to PROJECTS sheet BUT you cannot edit the data under PROJECTS because each submission overrides any edits and brings in the original form submission data -

What I would like to have happen is the data comes in Form Responses 1 sheet then copies to PROJECTS sheet on each submission but then does not override when a new submission comes in and any changes to the data under PROJECTS stays there

ISSUES - PROJECT NUMBER - the formula setup under Form Responses 1 sheet under column C row 2 - each row has a project number assigned and then it copies to the PROJECTS sheet, when the row is then archived ("DONE) and moves to the ARCHIVE sheet the project number assigned to that row goes with it BUT when you submit a new project the same number can be applied to a new project so then ultimately when you archive all the projects many will have the same project number, is there a way to make each project have a unique project number?

*ISSUES - SUBMIT FORM * - I am not sure why but when a new submission comes in the data is coming in at row 113 each time, not at the end of the form responses rows?

Any help would be greatly appreciated - I thought it was going to work but seems the script isn't the right setup for what I would like it to do

Thanks so much, Paola G

解决方案

I got a script thanks to Riël Notermans (Zzapps)

https://plus.google.com/u/0/116507112418725270540/posts/1i75t1wFEfh

function onFormSubmit(e){
  
 
  var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var projects =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PROJECTS");
  var lastrow = responses.getLastRow();
  var col = responses.getLastColumn();
  var row = responses.getRange(lastrow, 1, 1, col).getValues();

  projects.appendRow(row[0]);
  //copyValuesOnly(copyFromRange, copyToRangeStart);
}

Make sure there are no empty rows beneath both sheets, there are some project numbers. Just make sure the last row is the data.

这篇关于将提交的表单响应复制到新工作表（PROJECTS）并希望能够编辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！