Writing a google app script on protected range

Question

[edit] i finally got someone to unlock the range for me but i still get the error below. any insights?

I'm writing a gs to send data to MailChimp over the API. I can't even test to see if the function is working properly because so many of the sheets have protected ranges. This is a shared google sheet with upwards 30 users in it, tied to a form, so that's why it's protected (with edit access for everyone). Is there a way for me to hack into the authorization without requiring the document owner to unlock all the protected ranges? There are several sheets and several protected ranges, otherwise this would be a simple task to just unlock for me. I also work remote so explaining this process to an absolutely non-technical document owner is proving rather difficult..

I assume this has to do with protected ranges, but maybe I'm wrong? This is my error:

Method ScriptApp.newTrigger invoked in the global scope.Collapse
File: Attendance Email Line: 95
This function invocation will fail when the script is run as an Add-on in AuthMode.NONE.

Here is the main part that is the blocker, I think:

function onEdit(e) { 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var attendance = ss.getSheetByName("ATTENDANCE");
 attendance.activate();
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var currentSelection = SpreadsheetApp.getActiveSheet().getActiveSelection()
 var currentRow = currentSelection.getRowIndex();

 Logger.log(currentRow);
 Logger.log(currentSelection);

 return currentRow;  
 return currentSelection;

  var email = e.currentSelection[currentRow,2,1,1];
  var lead = e.currentSelection[currentRow,1,1,1];
  var latecount = e.currentSelection[currentRow,6,1,1];
  var calloffcnt = e.currentSelection[currentRow,7,1,1];
  var wfhcnt = e.currentSelection[currentRow,8,1,1];
  var wfhoccur = e.currentSelection[currentRow,9,1,1];
  var wfhremain = e.currentSelection[currentRow,10,1,1];
  var anncalloff = e.currentSelection[currentRow,11,1,1];
  var occurtotal = e.currentSelection[currentRow,21,1,1];
  var usedpto = e.currentSelection[currentRow,22,1,1];
  var usedsick = e.currentSelection[currentRow,23,1,1];
  var ptoremain = e.currentSelection[currentRow,24,1,1];
  var sickremain = e.currentSelection[currentRow,25,1,1];
      sendToMailChimp_(email,lead,latecount,calloffcnt,wfhcnt,wfhoccur,wfhremain,anncalloff,occurtotal,usedpto,usedsick,ptoremain,sickremain);

}

/**
 * Main function. Creates onEdit trigger.
 */

function myFunction (){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var attendance = ss.getSheetByName("ATTENDANCE");
  attendance.activate();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  }

  ScriptApp.newTrigger("myFunction")
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onEdit()
  .create();

**It's also important to note that none of this script is writing over anything in the sheet, it should just be read-only (so not a conflict with why ranges are protected)

mailchimp function for reference:

    function sendToMailChimp_(email,lead,latecount,calloffcnt,wfhcnt,wfhoccur,wfhremain,anncalloff,occurtotal,usedpto,usedsick,ptoremain,sickremain){
  var payload = {
    "apikey": API_KEY,
    "id": LIST_ID,
    "merge_fields[EMAIL]": email,
    "merge_fields[LEAD]": lead,
    "merge_fields[LATECOUNT]": latecount,
    "merge_fields[CALLOFFCNT]": calloffcnt,
    "merge_fields[WFHCNT]": wfhcnt,
    "merge_fields[WFHOCCUR]": wfhoccur,
    "merge_fields[WFHREMAIN]": wfhremain,
    "merge_fields[ANNCALLOFF]": anncalloff,
    "merge_fields[OCCURTOTAL]": occurtotal,
    "merge_fields[USEDPTO]": usedpto,
    "merge_fields[USEDSICK]": usedsick,
    "merge_fields[PTOREMAIN]": ptoremain,
    "merge_fields[SICKREMAIN]": sickremain,
    "double_optin": mc_double_optin,
    "update_existing": true
  };
  var payload = JSON.stringify;
  var options = {
    "method": "patch",
    "payload" : JSON.stringify(payload)
  };

  var response = UrlFetchApp.fetch(mc_base_url,options);
  Logger.log(response)
}

I don't think sheet or range has a method named getActiveSelection(). Also, you don't need to create an installable onEdit function if you already have a function named onEdit() — Cooper, Nov 03 '17 at 15:17
thanks! i did get the getActiveSelection from either someone else's successful script or the app site itself, but i might be missing the function related to it? what do you suggest i do to get the updated cell in reference to a specific column in this case? — Miranda Short, Nov 03 '17 at 15:20
Yes it is! Basically i need a way to get the updated cell corresponding with a particular row and the column that matches the data in mailchimp (in this case, the unifying datapiece is email) — Miranda Short, Nov 03 '17 at 15:29
If this doesn't run fast enough we can use getValues and get all of the data at one time into a two dimensional array but then instead of `var email=sh.getRange(currentRow,2).getValue()` we'd have to replace it with `var email =vA[currentRow-1][1]` where `vA=sh.getDataRange().getValues()` — Cooper, Nov 03 '17 at 15:52

Cooper · Accepted Answer · 2017-11-03T16:52:29.663

0

Try this:

function onEdit() 
{ 
 var ss=SpreadsheetApp.getActive();
 var sh=ss.getActiveSheet();
 var rg=sh.getActiveRange()
 if(sh.getName()=="ATTENDANCE")
 {  
   var currentRow = rg.getRow();
   var email = sh.getRange(currentRow,2).getValue();
   var lead = sh.getRange(currentRow,1).getValue();
   var latecount = sh.getRange(currentRow,6).getValue();
   var calloffcnt = sh.getRange(currentRow,7).getValue();
   var wfhcnt = sh.getRange(currentRow,8).getValue();
   var wfhoccur = sh.getRange(currentRow,9).getValue();
   var wfhremain = sh.getRange(currentRow,10).getValue();
   var anncalloff = sh.getRange(currentRow,11).getValue();
   var occurtotal = sh.getRange(currentRow,21).getValue();
   var usedpto = sh.getRange(currentRow,22).getValue();
   var usedsick = sh.getRange(currentRow,23).getValue();
   var ptoremain = sh.getRange(currentRow,24).getValue();
   var sickremain = sh.getRange(currentRow,25).getValue();
   sendToMailChimp_(email,lead,latecount,calloffcnt,wfhcnt,wfhoccur,wfhremain,anncalloff,occurtotal,usedpto,usedsick,ptoremain,sickremain);

  } 
}

edited Nov 03 '17 at 16:52

answered Nov 03 '17 at 15:46

Cooper

59,616
6
23
54

Thank you! I'm now having the same error I previously had, which was `Cannot read property "source" from undefined.` – Miranda Short Nov 03 '17 at 16:36
Does your function look like `onEdit(e)` – Cooper Nov 03 '17 at 16:38
yep, getting mailchimp function for you – Miranda Short Nov 03 '17 at 16:43
Is there more than one onEdit in the project? – Cooper Nov 03 '17 at 16:45
no other functions except the mailchimp function i mentioned above – Miranda Short Nov 03 '17 at 16:47
Okay. I changed the function. But there's something strange happening if were not getting the event object. – Cooper Nov 03 '17 at 16:53
Yes that was my blocker the past few days. No matter how i manipulated it wouldn't get the event object. The function now passes (hurray!), but nothing changed in mailchimp. I'm going to do more researching for that side. thanks so much for your help! – Miranda Short Nov 03 '17 at 16:54
By the way. Are you trying to test this function by running it from the Script Editor or from a menu. Because that doesn't generate the event object. You have to actually do an edit in the appropriate cells – Cooper Nov 03 '17 at 16:54
That's why you have not been getting the event object. So either of the last two would be appropriate. – Cooper Nov 03 '17 at 16:57

Writing a google app script on protected range

1 Answers1