[Discuss] gs menu item to run script

[eric.chadbourne at icloud.com (Eric Chadbourne)]

Super no problem.

Client says they want a couple of forms who's questions change depending on their answers.  They then want the forms to populate different tabs on a spreadsheet.  The manager who approves the line item for payment can put a value in a column.  When they are done approving they can go to a custom menu item and select my function that will send the information to their accountant and make in the spreadsheet that it has been processed.

My error was silly.  I merely got a var name wrong.  It totally worked as expected and was surprisingly simple.  I got frustrated and sent an email to BLU but within minutes saw my error.

Example function I want called:

var EMAIL_SENT = "EMAIL_SENT";
var APPROVED_EMAIL = "APPROVED_EMAIL";

function sendEmails_SALE() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2;  // First row of data to process
    var numRows = 5000;   // Number of rows to process.
    // Fetch the range of cells A2:B3.
    var dataRange = sheet.getRange(startRow, 1, numRows, 21);
    // Fetch values for each row in the Range.
    var data = dataRange.getValues();
    
    for (var i = 0; i < data.length; ++i) {
        var row = data[i];
        //var emailAddress = row[0];  // First column
        var emailAddress = "test.test at gmail.com";
        var emailApproved = row[19];
        var emailSent = row[20];
        var CooperatingAgentName = row[16];
        var BrokerFeeTotalonTransaction = row[18];
        
        var message1 = foo;
        var message2 = " is ";
        var message3 = bar;
        var message = message1 + message2 + message3;
        
        if (emailApproved == APPROVED_EMAIL) {
          // Prevents sending duplicates
          if (emailSent != EMAIL_SENT) {
            var subject = "Super secret";  //comment out for testing
            MailApp.sendEmail(emailAddress, subject, message);
            sheet.getRange(startRow + i, 21).setValue(EMAIL_SENT);  // set column for EMAIL_SENT here
            // Make sure the cell is updated right away in case the script is interrupted
            SpreadsheetApp.flush();
          }
        }
    }
}


Example custom menu item to call function:

/*
add a custom menu item for staff to run scripts
*/

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Process Forms')
      .addItem('Send apporoved SALE emails', 'menuItem1')
      .addSeparator()
      .addItem('Send apporoved RENTAL emails', 'menuItem2')
      .addToUi();
}

function menuItem1() {
  sendEmails_SALE()

  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
     .alert('Approved SALE info emailed to Foo');
}

function menuItem2() {
  sendEmailsRENTAL()


  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
     .alert('Approved RENTAL info emailed to Bar');
}

FWIW the google documentation is very good and I just copied it.  Any basic script writer can do some pretty cool automation stuff.

Thanks,

Eric Chadbourne
I really really need to find a regular client or a real job, else I be scripting under a bridge!


> On Jul 22, 2015, at 3:14 PM, John Boland <jj.boland at gmail.com> wrote:
> 
> can you elaborate and elucidate?
> 
> tia
> 
> On Jul 22, 2015 12:34 PM, "Eric Chadbourne" <eric.chadbourne at icloud.com> wrote:
> I got it working.  Silly error on my part.
> 
> Happy client, happy Eric.
> 
> Eric Chadbourne
> Nonprofit-CRM.org
> 
> > On Jul 22, 2015, at 12:18 PM, Eric Chadbourne <eric.chadbourne at icloud.com> wrote:
> >
> > Hi All,
> >
> > Anybody play around with google scripts?
> >
> > I have a client who wants to be able to click a button on a google spreadsheet and have some custom functions kick off, which includes sending an email.
> >
> > I whipped up a script (I don't really know javascript but the syntax isn't hard) and everything works.  But the user has to go to Tools => Script Editor => foo.gs.  I would rather I could add a custom menu item and when they click this it kicks off my function.  This currently does not work and gives no error.
> >
> > From what I understand a custom menu item has to use a "trigger" onOpen() and triggers can't use functions that require authorization such as sending an email.
> >
> > My question is there anyway to get this to work the way the client wants?  The onOpen() is used to created the menu button, but the menu item calls my custom function.
> >
> > Thanks for any tips,
> >
> > Eric Chadbourne
> > Nonprofit-CRM.org
> >
> 
> _______________________________________________
> Discuss mailing list
> Discuss at blu.org
> http://lists.blu.org/mailman/listinfo/discuss