CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user_87625129
user_87625129

Reputation: 89

How can I tell if a Google Sheet is shared?

I am developing an Google Sheets script that in order for it to work the Sheet needs to be shared publicly. Is there a way to get whether or not the Sheet is shared via an apps script? (Note, I am not interested in getting the shared settings for the script itself but for the Sheet).

function onInstall() {
  onOpen();
}

function onOpen() {
  SpreadsheetApp.getUi().createAddonMenu().addItem("myitem", "item").addToUi(); 
}

function item(){
  var title = 'item';
  var html = HtmlService.createTemplateFromFile('item');
  var htmlOutput = html.evaluate();
  htmlOutput.setTitle(title).setWidth(100).setHeight(150)
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, title);
}

Upvotes: 0

Views: 154

Answers (2)

Wicket
Wicket

Reputation: 38150

You will need to use the Drive Service or the Drive Advanced Service.

Let say that your script is bounded to a spreadsheet. The following function will log true if the spreadsheet is shared and otherwise it will log false.

function myFunction() {
  Logger.log(isShared());
}


function isShared(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var id = spreadsheet.getId();
  var file = DriveApp.getFileById(id);
  var editors = file.getEditors();
  var viewers = file.getViewers();
  var sharing = file.getSharingPermission();
  return editors.length > 0 || viewers.lenght > 0 || sharing !==  DriveApp.Permission.NONE;

}

If you are only interested in knowing if the spreadsheet is shared to anyone with the link (assuming that the spreadsheet was created with a personal account)

function isSharedPublicly(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var id = spreadsheet.getId();
  var file = DriveApp.getFileById(id);
  var sharing = file.getSharingPermission();
  return sharing !==  DriveApp.Permission.NONE;

}

Upvotes: 4

Cooper
Cooper

Reputation: 64040

function findPublicSpreadsheets() {
  const files=DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  while(files.hasNext()) {
    let file=files.next();
    if(file.getSharingAccess()==DriveApp.Access.ANYONE) {
      //is this is a file your looking for?
    }
  }
}

Upvotes: 1

Related Questions