onFormSubmit Help

[Eric]

Hello, 

kk\Each time a form is submitted for a new order, I want to copy some of that data (the newest row, starting column is 4, number of rows is 1, and number of columns is 7) and paste it into a different sheet.  

I only want the values so the destination sheet's formatting isn't changed.  

On the destination sheet, once an order is processed and a box is checked, a script on that sheet copies information to a completed tab and deletes the line.  

I'm having a hard time understanding how to create an onFormSubmit trigger to bring the data over to the other sheet.  Here's what I have so far but I'm very new:

ScriptApp.newTrigger("submitSO")
  .forForm(FormApp.getActiveForm())
  .onFormSubmit()
  .create();

function submitSO() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(),
  dest = SpreadsheetApp.openById("Sheet ID").getSheetByName("OpenSO");
  formSO = ss.getSheetByName("FormSO");
  lastRowData = formSO.getRange(formSO.getLastRow(), 4, 1, 7).setValue(dest.getRange(dest.getLastRow() + 1, 2, 1));
}

Thanks for pointing me in the right direction.

Eric

[cbmserv...@gmail.com]

Hi Eric,

 

What I would recommend is that you get the form to store all responses directly into a Spreadsheet and then put the script on the spreadsheet and let it copy over the specific cells you want to your specific spreadsheet. This makes it much easier to troubleshoot problems and to retrieve any data in case your script fails to run for whatever reason.

 

I would not bother defining code to activate the trigger. It is a simple task to define it manually via the new editor. A programmatic definition of triggers is great if you need to change the triggers during execution (it does not sound like you need to in your case).

 

The triggered function will get an event object which will define what row/data/etc.. was added as a result of the triggered event. So do use the event object to help you track down the changes easier.

 

Here is a simple example of what your triggered function could look like:

 

function trigOnSubmit(e) 

{

  var s = SpreadsheetApp.getActiveSheet();  // this is how you should grab the sheet where data is entered

  var row = e.range.getRow();               // this is the row number where data was entered.

  var dataEntered = e.values;               // values is all the data that was entered into spreadsheet.

  var dest = SpreadsheetApp.openById("Sheet ID").getSheetByName("OpenSO");

  dest.appendRow(dataEntered); // this will add a row at bottom of your sheet with all values entered

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/912c3d09-f007-4f2f-8e09-169fe93d5c1dn%40googlegroups.com.

[Eric]

Thank you for the recommendation but I'm running into a snag regarding the "row" and "s" variable since they aren't being used/referenced in the code:

function trigOnSubmit(e) 

{

  var s = SpreadsheetApp.getActiveSheet();  

  var row = e.range.getRow(); // TypeError: Cannot read properties of undefined (reading 'range')

  var dataEntered = e.values;           

  var dest = SpreadsheetApp.openById("SheetID").getSheetByName("OpenSO");  // I only want certain information displayed on the "OpenSO" sheet where my operations staff will be working, is there a way to select only specific columns, maybe by column number or matching title? 

  dest.appendRow(dataEntered);

}

I included an attachment to better demonstrate what I'm trying to accomplish. 

Thanks again,

Eric

[CBMServices Web]

The onEdit event object e is only available when triggered automatically by an edit of the spreadsheet. If you call the function manually, you will get this error.

The variable s should work if this code is in a spreadsheet container. If you have it as a standalone file, then it will not work.

So, add the code inside the spreadsheet that will receive the form submissions. Setup the trigger to be on form submit,  and test it by submitting a form.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/fe9d8c01-5005-4957-b1c6-aecc81a875f3n%40googlegroups.com.

[Eric]

Ugh...I knew that too.  Thank you, it's working as intended.  

Can I specify what is sent to the destination sheet to specific columns (I don't want the timestamp and email included)?  

Is there a way to bring in only values so the formatting in the destination sheet/cells is applied (like when you copy a cell and select "paste special -> values only" to maintain the target format)?

Thanks,

Eric

[CBMServices Web]

The e.values gives you all the data that the form collected. You can exclude some items out of it as you wish, it is just an array and you can drop some items out of it.

The appendRow method appends a whole new row at bottom of spreadsheet. If you want to only set certain cells, then don't use it, but use the getRange() method to specify where you want to copy it to and setValues() to set the values. Formatting can be copied from previous row to the new row to make it the same.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e3694348-eae0-4fa0-981a-31aa1df9cff8n%40googlegroups.com.