I think I have a trigger problem

[Gina Welch]

I have an inventory spreadsheet I am working on. When a new form response comes in, it goes to the Add Tile Form. The responses are automatically copied to the Tile sheet where calculations are done. I need to ARCHIVE older rows that match the Box ID when a new row is added. It is working almost like it should, except the trigger. I have to make a change to the sheet after a new line comes in for it to trigger. I am very green at this, so I am begging for some help, I even said a little prayer before I opened my computer, LOL!! I think you should have all the access you need to play around with this. It is a copy, so have at it. 

Form link https://forms.gle/UKSzLoi3skSmSDTV8

GS Link  https://docs.google.com/spreadsheets/d/1Ar0frggxGBcfzSoCF9EzcIW9Q4BmgNZatQx1Gy8_DYg/edit?usp=sharing

[Gary Odums]

To begin receiving events about form submissions, you must first set up a trigger.

There are two methods to add a trigger to a script.

Methods demonstrated below are: Programmatically and Web UI.

Web UI method:

1. Find the "Triggers" icon here (image of an alarm clock)
   
   
   
2. From the Triggers view, select "+ Add Trigger"
   
   
   
3. Choose which function to trigger when a new form submission event occurs
   
   
   
4. Change the selected event type to "On form submit"
   
   
   
   
5. Click "Save" to activate the trigger
   
   

Triggers can be added and removed at any time from this web UI view.


Programmatic method (I strongly recommend using the web UI method instead of this method if you are just getting started):

onFormSubmit() 

Specifies a trigger that will fire when the spreadsheet has a form submitted to it.

const sheet = SpreadsheetApp.getActive(); ScriptApp.newTrigger('myFunction') .forSpreadsheet(sheet) .onFormSubmit() .create();

More details about creating triggers programmatically can be found here: https://developers.google.com/apps-script/reference/script/spreadsheet-trigger-builder#onformsubmit
---

After you have completed either of the described methods above for creating a form submission trigger, the function you selected to be triggered will begin receiving an event object (e). More details about this event object can be found here: https://developers.google.com/apps-script/guides/triggers/events#form-submit.

Please reference the event object properties linked above. Note that e.changeType is not a valid property, you may notice that the spirit of what you attempted here is captured when setting up the trigger.

Feel free to ask any further questions.

Thanks

[Gina Welch]

The form responses go to a different sheet (add tile form) and are copied to my calc sheet (Tile) with a QUERY. I am guessing that this could be my problem, or is there a tweak I need to make in my script? I had already set up the trigger on edit and on change and neither if those worked, and when I just tried on form response, it still did the same thing. I literally googled all of my parameters and copied the script it gave me. I did not write it. I am open to whatever fixes you might have. Thanks!!

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/vDWO3zJBD6w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/0bbe1b6f-e219-4cb2-ac04-2c165d23752fn%40googlegroups.com.

--

Thanks,

Gina Welch

[Gary Odums]

I have added the script shown below to the sample spreadsheet provided. Along with this script, I have added a sheet that allows for creating new custom columns and updating existing custom columns. The only remaining step to completing the setup of this mock spreadsheet is to add the On form submit trigger to the receiverOfFormSubmission function.

In this script, when an On form submission event is received by the receiverOfFormSubmission(e) function then the new row, which has already been added to the ADD TILE FORM sheet from the form submission, will now be updated to include all of the additional columns that are defined in the MyTemplate sheet. Any formatting applied to the custom column formula cells in the MyTemplate sheet will also be automatically applied to the corresponding custom columns in the ADD TILE FORM sheet.

The MyTemplate sheet consists of two important formulas. The first is in cell A1 (=ARRAYFORMULA('ADD TILE FORM'!1:1)), this formula makes visible every header from the ADD TILE FORM sheet. The second important formula on the MyTemplate sheet is in cell A2 (=ARRAYFORMULA('ADD TILE FORM'!A2:L2)), this formula makes visible the form's question response fields. As you continue moving right on the MyTemplate sheet, you will find the custom column headers in row 1 with their unique formula definitions in row 2. ** ONLY these formula definitions can be updated from the MyTemplate sheet. NEW custom columns must be added after the last column** Details regarding steps to take when making changes to the form (adding/removing questions) and when making changes to the MyTemplate sheet (adding/removing custom columns) can be found in the source code.

Additionally, in using this script, you will find a new menu appear near the top of the window named "My Menu" with the single option "Archive every row". Selecting this option will update every cell in the custom column labeled "ARCHIVED" to be set to ARCHIVED.

const templateSheet = getSheetByName("MyTemplate");

const formResponsesSheet = getSheetByName("ADD TILE FORM");

const archivedColumnLabel = "ARCHIVED";

const archivedColumnValue = "ARCHIVED";

function getSheetByName(sheetName) {

  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

}

function getRowRange(sheet,row) {

  return sheet.getRange(row,1,1,sheet.getLastColumn());

}

function getColumnRange(sheet,column) { // omits header

  return sheet.getRange(2,column,sheet.getLastRow(),1);

}

function getCellRange(sheet,row,column) {

  return sheet.getRange(row,column,1,1);

}

// -- Adding and removing form questions --

// Custom headers should always be added from the Form Responses sheet and not to the template sheet directly

// Adding a question to the form would require subsequently inserting a column into the template sheet directly after the column for the last question on the form.

// Deleting a question from the form would require subsequently deleting the blank column that appears directly after the column for the last question on the form.

// -- Adding and removing custom fields --

// All custom fields are tracked by their name. This means they can freely be added or removed from the FORM RESPONSES sheet as necessary. Just remember to shift over the custom field formulas such that they continue to align with their corresponding custom field header label.

function addTemplateFormulasToFormResponseSheet(templateSheet,formResponsesSheet,rowIndexStart,totalNewRows) {

  const templateSheetHeaderRange = getRowRange(templateSheet,1);

  const formResponsesSheetHeaderRange = getRowRange(formResponsesSheet,1);

  const formResponsesSheetHeaderValues = formResponsesSheetHeaderRange.getValues().flat();

  const templateSheetHeaderValues = templateSheetHeaderRange.getValues().flat();

 

  const templateSheetFormulaRowIndex = 2;

  const templateSheetFormulaCells = Array.from({length:templateSheetHeaderValues.length}).map(

    (v,i)=>getCellRange(templateSheet,templateSheetFormulaRowIndex,i+1)

  );

  const sortedTemplateSheetFormulaCells = formResponsesSheetHeaderValues.map(formResponsesHeaderValue=>{

    const headerIndex = templateSheetHeaderValues.indexOf(formResponsesHeaderValue);

    if (headerIndex==-1) { return ""; } else { return templateSheetFormulaCells[headerIndex] };

  });

  sortedTemplateSheetFormulaCells.forEach((sortedTemplateSheetFormulaCell,i)=>{

    if (sortedTemplateSheetFormulaCell.getFormula() == "") { return; }

    const formResponsesSheetNewRowCell = getCellRange(formResponsesSheet,rowIndexStart,i+1);

    if (formResponsesSheetNewRowCell.getValue() != "") { return; }

    sortedTemplateSheetFormulaCell.copyTo(formResponsesSheetNewRowCell);

    formResponsesSheetNewRowCell.setValue(formResponsesSheetNewRowCell.getValue());

  });

}

function receiverOfFormSubmission(e) {

  const newRowIndexStart = e.range.rowStart;

  const newRowIndexEnd = e.range.rowEnd;

  const totalNewRows = 1 + (newRowIndexEnd-newRowIndexStart);

  addTemplateFormulasToFormResponseSheet(templateSheet,formResponsesSheet,newRowIndexStart,totalNewRows);

}

function archiveEveryRow() {

  const headersArray = getRowRange(formResponsesSheet,1).getValues().flat();

  const archivedColumnIndex = headersArray.indexOf(archivedColumnLabel)+1;

  if (archivedColumnIndex == 0) { return; }

  const archivedColumnValues = getColumnRange(formResponsesSheet,archivedColumnIndex).getValues().flat();

  archivedColumnValues.forEach((v,i)=>{

    if (v != archivedColumnValue) { getCellRange(formResponsesSheet,i+1,archivedColumnIndex).setValue(archivedColumnValue); }

  });

}

function onOpen() {

  SpreadsheetApp.getUi().createMenu('My Menu')

      .addItem('Archive every row', 'archiveEveryRow')

      .addToUi()

}

---

Below are instructions to verify or change the spreadsheet form responses are saved to:

Choose where to store responses

If you store your responses in a spreadsheet, Google Sheets automatically puts your data in a table. It gives your data format and structure. Learn how to use tables in Google Sheets.

1. Open a form in Google Forms.
2. At the top, click Responses  Summary.
3. At the top right, click More   Select destination for responses.
4. Select an option:
   • Create a new spreadsheet: Creates a spreadsheet in Google Sheets.
   • Select existing spreadsheet: Select the existing spreadsheet that you want to store the responses in.
5. Click Create or Select.

Source: https://support.google.com/docs/answer/2917686?hl=en

Feel free to ask any further question or make any requests.