CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
JamesReed68
JamesReed68

Reputation: 419

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 [email protected]
https://docs.google.com/spreadsheets/d/2 [email protected]

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?

Upvotes: 1

Views: 62

Answers (1)

Tanaike
Tanaike

Reputation: 201388

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

References:

Upvotes: 2

Related Questions