Google spreadsheets, giving permission to user with google script

Question

My goal:

Every time I create a new spreadsheet, it should automatically give permission to "[email protected]" as an editor.

Research:

  function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  ss.addEditor('[email protected]');
  }

Issue: Am actually using selenium automation to create the spreadsheet and gspread to read the values from it but the issue is gspread only reads spreadsheets shared with the service account...

I tried manually going to tools > script editor and run the program above, it worked. but when a new spreadsheet is created and I ran the same program but it didn't work.

Conclusion: Every time selenium creates a new spreadsheet it should automatically share it to the service account.

The code that you gave is getting the active spreadsheet, which must be the spreadsheet that the code is bound to. If you are creating a new spreadsheet, then the code that created the new spreadsheet is not bound to that new spreadsheet. You need to get the newly created spreadsheet's file ID, and then get the spreadsheet by it's file ID. Where is the code that is creating the new spreadsheet? — Alan Wells
New spreadsheet is actually created by the Meet Attendance extension once the attendance is taken.I wrote a program with selenium, python where it automatically joins the meeting, takes attendance with Meet attendance extension and then opens the spreadsheet where the data is stored.I wrote another program which reads the data stored in the spreadsheet using gspread and gives the output of the people who are absent to the class.But the issue is spreadsheet must be shared to the service account in order to read data from it, I cant manually share to the service account all the time — Jiya
How can I get spreadsheet's file Id with the help of google script? — Jiya
So, the real issue is getting the new Google Sheet file ID, and then sharing it to the service account? — Alan Wells

Nabnub Nabnub · Accepted Answer · 2020-05-29T20:17:14

If you create a follow up spreadsheet, and assuming that the files that you create are within a specific folder, the following code will help you to list the files (this is optional in the script but could be useful) and share them to the specified email at the same time. You can either run the script from the menu created (My Files) within the spreadsheet or set a trigger to run it automatically at a specific times/days:

So, you need:

To create a new spreadsheet for listing the files
Get the ID of this spreadsheet ( // https://drive.google.com/drive/folders/ID IS HERE )
Get the ID of the folder containing the created spreadsheets (https://docs.google.com/spreadsheets/d/ID IS HERE/edit)
Set the email to share the files with
Run the script manually each time or set up a trigger to do it automatically

Code:

   // ---------- Menu ----------
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('My Files')
  .addItem('List & Add Permission', 'ListFilesandAddEditor')
  .addToUi();
}

function ListFilesandAddEditor() {

  var folder = DriveApp.getFolderById('FOLDER ID GOES HERE');  
  var sheetId = "SPREADHSEET ID GOES HERE";
  var ss = SpreadsheetApp.openById(sheetId);

  // specify the sheet to write the DATA, here it's Sheet1

  var sheet = ss.getSheetByName("Sheet1"); 
  sheet.getRange("A2:B").clear();

  var output = [];
  var file;
  var name;
  var link;
  var fileId;
  var contents = folder.getFiles();
  while(contents.hasNext()) {
    file = contents.next();    
    data = [
      name =  file.getName(),
      link =  file.getUrl(),
      fileId = file.getId(),
      DriveApp.getFileById(fileId).addEditor('[email protected]'),
    ];

    output.push([name, link]);

    // write data to the sheet  
    sheet.getRange(2, 1, output.length, output[0].length).setValues(output);  
  }  
};

Google spreadsheets, giving permission to user with google script

1 Answers