Question

0

205

Views

Google App Script: Share spreadsheets in Column 1, with Users in Column 2

Objective

On script run, give view access for all of the emails (column B), to all of the spreadsheets (column A)

The Spreadsheet looks like:

Sheet URL's	Emails
https://docs.google.com/spreadsheets/d/1	Bob@email.com
https://docs.google.com/spreadsheets/d/2	John@email.com

The script

function setSheetPermissions(){
  //Spreadsheet that contains the Spreadsheet URL's & Emails.
  var ss= SpreadsheetApp.openById('spreadsheetID')
  
  // Sheet name that contains the URL's & Emails
  var sheet = ss.getSheetByName("Sheet1")
  
  // Get the values of all the URL's in column A
  var getSheetURLs = sheet.getRange("A2:A50").getValues();

  // Get the values of all the emails in column B
  var getEmails = sheet.getRange("B2:B50").getValues();
  
  for ( i in getEmails)
     getSheetURLs.addViewer(getEmails[i][0])

}

Problem / Error

getSheetIDs.addViewer is not a function (line 26, file "Code")

Line 26: getSheetURLs.addViewer(getEmails[i][0])

What am I doing wrong?

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-05T05:48:50.144Z

Modification points:

You can retrieve both values from the columns "A" and "B".
In your script, getSheetURLs is a 2 dimensional array. In this case, the method addViewer cannot be directly used. I thought that this is the reason for your error message.

When these points are reflected in your script, it becomes as follows.

Modified script:

From:

// Get the values of all the URL's in column A
var getSheetURLs = sheet.getRange("A2:A50").getValues();

// Get the values of all the emails in column B
var getEmails = sheet.getRange("B2:B50").getValues();

for ( i in getEmails)
   getSheetURLs.addViewer(getEmails[i][0])

To:

var values = sheet.getRange("A2:B" + sheet.getLastRow()).getValues();
values.forEach(([sheetUrl, email]) => {
  if (sheetUrl && email) SpreadsheetApp.openByUrl(sheetUrl + "/edit").addViewer(email);
});

From your showing sample Spreadsheet, I understood that your Spreadsheet URL is like https://docs.google.com/spreadsheets/d/###. In this case, openByUrl cannot be directly used. So I added /edit. If your showing Spreadsheet URLs are different from your sample Spreadsheet, please provide your sample Spreadsheet URL. By this, I would like to modify the script.
If your actual Spreadsheet URLs are both https://docs.google.com/spreadsheets/d/### and https://docs.google.com/spreadsheets/d/###/edit, you can also use SpreadsheetApp.openByUrl(sheetUrl + (sheetUrl.includes("/edit") ? "" : "/edit")).addViewer(email).

0

205