Share file restriction

58 views
Skip to first unread message

Andrew Apell

unread,
Feb 8, 2020, 12:51:41 PM2/8/20
to Google Apps Script Community

How does one go about restricting/blocking the usage of an spreadsheet add-on in a shared file? Is it even possible?

Steve Webster

unread,
Feb 8, 2020, 3:49:50 PM2/8/20
to google-apps-sc...@googlegroups.com
Hmm. Good question. Natively the Sheets "owner" can restrict access to the spreadsheet. 

If the use case of allowing editors to the spreadsheet AND you want to restrict where only the "owner" can run the add-on, then you may need to add some code to check active user versus the sheet owner. then more code to notify the "editors" this add-on is intended for spreadsheet "owners" only.

Code examples: 

SpreadsheetApp service:
getViewers()

Kind Regards,

Steve Webster
SW gApps LLC, President 
Google Product Expert in: Google Apps Script, Drive, and Docs 
Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 
Add-ons: Text gBlaster and Remove Blank Rows


On Sat, Feb 8, 2020 at 12:51 PM Andrew Apell <chis...@gmail.com> wrote:

How does one go about restricting/blocking the usage of an spreadsheet add-on in a shared file? Is it even possible?

--
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/99280317-cf12-4b88-8789-6bf58524f51f%40googlegroups.com.

Andrew Apell

unread,
Feb 8, 2020, 4:17:17 PM2/8/20
to google-apps-sc...@googlegroups.com
Thanks Steve... so it's Session.getActiveUser().getEmail() not Session.getEffectiveUser().getEmail()?

EDIT: getEditors() is promising

On Saturday, 8 February 2020 23:49:50 UTC+3, Steve Webster wrote:
Hmm. Good question. Natively the Sheets "owner" can restrict access to the spreadsheet. 

If the use case of allowing editors to the spreadsheet AND you want to restrict where only the "owner" can run the add-on, then you may need to add some code to check active user versus the sheet owner. then more code to notify the "editors" this add-on is intended for spreadsheet "owners" only.

Code examples: 

SpreadsheetApp service:
getViewers()

Kind Regards,

Steve Webster
SW gApps LLC, President 
Google Product Expert in: Google Apps Script, Drive, and Docs 
Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 
Add-ons: Text gBlaster and Remove Blank Rows


On Sat, Feb 8, 2020 at 12:51 PM Andrew Apell <chis...@gmail.com> wrote:

How does one go about restricting/blocking the usage of an spreadsheet add-on in a shared file? Is it even possible?

--
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-community+unsub...@googlegroups.com.

Andrew Roberts

unread,
Feb 9, 2020, 7:05:42 AM2/9/20
to google-apps-sc...@googlegroups.com
Yep. The active user is the one actually logged in, the effective user is the one who initially authorised the script.

On Sat, 8 Feb 2020 at 21:17, Andrew Apell <chis...@gmail.com> wrote:
Thanks Steve... so it's Session.getActiveUser().getEmail() not Session.getEffectiveUser().getEmail()?
On Saturday, 8 February 2020 23:49:50 UTC+3, Steve Webster wrote:
Hmm. Good question. Natively the Sheets "owner" can restrict access to the spreadsheet. 

If the use case of allowing editors to the spreadsheet AND you want to restrict where only the "owner" can run the add-on, then you may need to add some code to check active user versus the sheet owner. then more code to notify the "editors" this add-on is intended for spreadsheet "owners" only.

Code examples: 

SpreadsheetApp service:
getViewers()

Kind Regards,

Steve Webster
SW gApps LLC, President 
Google Product Expert in: Google Apps Script, Drive, and Docs 
Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 
Add-ons: Text gBlaster and Remove Blank Rows


On Sat, Feb 8, 2020 at 12:51 PM Andrew Apell <chis...@gmail.com> wrote:

How does one go about restricting/blocking the usage of an spreadsheet add-on in a shared file? Is it even possible?

--
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.

--
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/99893ba4-c8e1-4d52-9a6e-1d6002055dc5%40googlegroups.com.

Andrew Apell

unread,
Feb 9, 2020, 7:09:58 AM2/9/20
to google-apps-sc...@googlegroups.com
I tried to use activeUser in a test file but, when 2 users were using the same sheet and one happened to be fully authorised, the 2nd party also had full access.

On Sunday, 9 February 2020 15:05:42 UTC+3, andrew wrote:
Yep. The active user is the one actually logged in, the effective user is the one who initially authorised the script.

On Sat, 8 Feb 2020 at 21:17, Andrew Apell <chis...@gmail.com> wrote:
Thanks Steve... so it's Session.getActiveUser().getEmail() not Session.getEffectiveUser().getEmail()?

On Saturday, 8 February 2020 23:49:50 UTC+3, Steve Webster wrote:
Hmm. Good question. Natively the Sheets "owner" can restrict access to the spreadsheet. 

If the use case of allowing editors to the spreadsheet AND you want to restrict where only the "owner" can run the add-on, then you may need to add some code to check active user versus the sheet owner. then more code to notify the "editors" this add-on is intended for spreadsheet "owners" only.

Code examples: 

SpreadsheetApp service:
getViewers()

Kind Regards,

Steve Webster
SW gApps LLC, President 
Google Product Expert in: Google Apps Script, Drive, and Docs 
Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 
Add-ons: Text gBlaster and Remove Blank Rows


On Sat, Feb 8, 2020 at 12:51 PM Andrew Apell <chis...@gmail.com> wrote:

How does one go about restricting/blocking the usage of an spreadsheet add-on in a shared file? Is it even possible?

--
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-community+unsub...@googlegroups.com.

--
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-community+unsub...@googlegroups.com.

Andrew Apell

unread,
Mar 5, 2020, 1:39:24 PM3/5/20
to Google Apps Script Community
I need clarity on this:

When an add-on is installed via domain for all users, who's email do we get with getEffectiveUser()? The admin or the individual users?

On Sunday, 9 February 2020 15:05:42 UTC+3, andrew wrote:
Yep. The active user is the one actually logged in, the effective user is the one who initially authorised the script.

On Sat, 8 Feb 2020 at 21:17, Andrew Apell <chis...@gmail.com> wrote:
Thanks Steve... so it's Session.getActiveUser().getEmail() not Session.getEffectiveUser().getEmail()?

On Saturday, 8 February 2020 23:49:50 UTC+3, Steve Webster wrote:
Hmm. Good question. Natively the Sheets "owner" can restrict access to the spreadsheet. 

If the use case of allowing editors to the spreadsheet AND you want to restrict where only the "owner" can run the add-on, then you may need to add some code to check active user versus the sheet owner. then more code to notify the "editors" this add-on is intended for spreadsheet "owners" only.

Code examples: 

SpreadsheetApp service:
getViewers()

Kind Regards,

Steve Webster
SW gApps LLC, President 
Google Product Expert in: Google Apps Script, Drive, and Docs 
Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 
Add-ons: Text gBlaster and Remove Blank Rows


On Sat, Feb 8, 2020 at 12:51 PM Andrew Apell <chis...@gmail.com> wrote:

How does one go about restricting/blocking the usage of an spreadsheet add-on in a shared file? Is it even possible?

--
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-community+unsub...@googlegroups.com.

--
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-community+unsub...@googlegroups.com.

Alex

unread,
Mar 6, 2020, 12:30:22 PM3/6/20
to Google Apps Script Community
There is the real code from my add-on

/**
 * Checks if the current user is owner
 * @returns {boolean}
 */
function isThereOwner() {
  var ow = SpreadsheetApp.getActive()
    .getOwner()
    .getEmail();
  var au = getMeEmail();
  return ow == au;
}

function getMeEmail() {
  var responce = UrlFetchApp.fetch("https://www.googleapis.com/oauth2/v2/userinfo?fields=email" , {
    headers: {
      Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
    },
    "muteHttpExceptions": true
  });
  if(responce.getResponseCode() < 400){
    return JSON.parse(responce.getContentText()).email;
  } else {
    return undefined;
  }
}

It's not a panacea but it works fine.

Best, Alex.
Reply all
Reply to author
Forward
0 new messages