Copy submitted Form Responses to a New Sheet (PROJ

问题:

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

回答1:

Here's some pseudo-steps based on how I handle this kind of situation:

1. onEdit trigger runs when an edit is made to the Spreadsheet
2. onEdit checks your range for the value that you're looking for. If the value(s) match, proceed to step 3. Otherwise, script does nothing.
3. Get the row number where the value is changed to the one you're looking for.
4. Get the Range of that row up to/including whatever column you want to stop at.
5. Create an Array (or Object) that is a copy of all that data.
6. "Paste" (add a new row) to the 'completed' Spreadsheet that is the data in that Array (or Object).
7. Erase the values in the old Spreadsheet.

However, because Form responses are critically tied to the response Spreadsheet, moving rows as you are trying to do is not the best way to go about the problem.

In fact, I would dare to say that you should not be moving at all - instead, what you can do is copy the values from the row you're looking for "DONE" in, and then simply hide that row in the response Spreadsheet. That way, you can think of rows that are not hidden as "still need to be finished.", and projects that are finished will not only be in the response Spreadsheet for "archival/data integrity" purposes, but also in your "completed" Spreadsheet that you can make public-facing, etc. without needing to worry about your Form responses being modified.

Personally I use this strategy for scheduling project reservations for foreign language faculty at my university, and it greatly simplifies the "extra work" required in managing two spreadsheets concurrently. Instead, just hide values you don't need to see and post them to a new Spreadsheet that can act as a record of what's important, all while being able to edit the layout of the Spreadsheet.

PS - If you didn't notice already, you can't break the integrity of the response Spreadsheet, otherwise the Form link would be broken. In fact, Google won't let you make layout-changing edits to response Spreadsheets that are linked to a Form.

回答2:

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.