Google Form > Google Sheet > Google Code = email notification

Question

I wasn't sure where I should post this. I hope this is the right place.

I have a Google Form, the responses are automatically fed into a Google Sheet when the form is filled out. As I'm the Owner I have setup the default Google Notifications that tells me the Form has changed whenever the Google Form is filled out:

The process is: Google Form filled in > Google Sheet updated with responses from Form > Email Notification to my email address

I have shared the Google Sheet to another user in my business. They have Edit rights to the Sheet. I want this user to also receive a notification when the form has a change.

So process would be: Google Form filled in > Google Sheet updated with responses from Form > Email Notification to mine and the other users email address

I've searched online as it isn't possible in Google Apps yet (it only notifies the owner). It was advised to create a custom Google Code on my Google Sheet like this:

function myFunction() {
    MailApp.sendEmail("[e-mail]","Notification - Change in Spreadsheet","Notification - There has been a change in the Spreadsheet. Please view the change here: (I've put the url of sheet here) ");

    MailApp.sendEmail("[e-mail]","Notification - Change in Spreadsheet","Notification - There has been a change in the Spreadsheet. Please view the change here: (I've put the url of sheet here) ");
}

When I run this code from the Code Editor it sends me and the other user the custom Google Code notification email which I want.

When I fill in the form, the responses update in the Sheet, I get a email notification from the official Google Notifications of the change. The other user and I do not get the custom Google Code email notification though.

I've had a look at the Google Code website on the Triggers section but not sure how to write this part. I think I need to write a trigger on the code that says when the Sheet has changed run the custom code.

So Google Form filled in > Google Sheet updated with responses from Form > change in Google Sheet sets off a trigger > trigger runs custom Google Code > the other user and I receive the custom Google Code email notification.

Can anyone help with the trigger code part or even be able to recommend a different solution?

Thanks.

Answer 1

You can add a trigger in the "Resources" -> "Current Project's Triggers" menu item from your script editor.

then create a trigger in the following format:

"myFunction" -> "From Spreadsheet" -> "On Change"

Answer 2

For the trigger, I've made a similar code before, but that is for event onChange. What you clearly need here is "OnFormsubmit" trigger. Below is the code I've made with custom menu with inline comments;

// Written by Madzihi
// on a nice Sunday afternoon

function onOpen(){    //Setting up all the custom menu components
        var ss = SpreadsheetApp.getActiveSpreadsheet();    //Get the current active Spreadsheet
        ss.addMenu('Custom Setup',[{name:'Select Sheet',   //Assemble all the menu's component
                                functionName:'openSheet'},{name:'Set Trigger',functionName:'triggerSet'},
                                {name:'Delete Trigger',functionName:'delTrigger'}]);
        ss.addEditor(Session.getActiveUser().getEmail());   //This line set up so editor(s) don't have to run manually the script to gain authorization
}
function triggerSet(){    //Function to setting up the triggers for current project 
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ui = SpreadsheetApp.getUi();    //Get the UI for setting trigger
    var triggers = ScriptApp.getProjectTriggers();    //Get the current project's trigger
    var respond = ui.alert('Setting up new trigger', ui.ButtonSet.OK_CANCEL);    //Set the UI prompt message and 'OK/CANCEL' buttons
        if(respond ==ui.Button.OK && triggers <=1){   //Set the trigger if button 'OK' is clicked and prevent trgger to be installed more than 1
        ScriptApp.newTrigger('onChanges').forSpreadsheet(ss).onChange().create();   //Line for creating the trigger based on changes that made in google sheet
        }
        else if (respond == ui.Button.CANCEL){    //If 'CANCEL' button is clicked will alert user no trigger installed
        ui.alert('No new trigger have been installed');
        }
        else { ui.alert('No new trigger have been installed')    //If close button is clicked will alert user no trigger installed
        }
}
function delTrigger(){    //Setup for deleting trigger
    var ui= SpreadsheetApp.getUi();
        try{
        var triggers = ScriptApp.getProjectTriggers();
        ScriptApp.deleteTrigger(triggers[0]);    //Choosing the trigger to be deleted
        ui.alert('Your trigger have been deleted');
           }
        catch(e){
           ui.alert('No trigger have been set!');    //If there is no trigger have been set, it will throw this alert to user.
        }
}

This line should be replacing the current trigger builder in the code I've made. The editor and you will need to click any of the custom menu once to activate the authorization.

ScriptApp.newTrigger('onChanges').forForm(key).onFormSubmit().create(); 

Explanation on this line above:

• 'onChange' is the function name that will be triggered
• 'key' is the key/unique id of the form bind with the spreadsheet.

Hope this help you with the trigger, if anything comments here.