'Google App Scripts & Google Sheets - When email added to cell, automatically add edit access for that email
It this possible with Google App Scripts?
Using Google Forms the user will add their email in the text field, and click submit. The data is then sent to Google Sheets (working). Here is what the results look like.
| Timestamp | Something | |
|---|---|---|
| 01/01/2022 | [email protected] | ... |
| 01/02/2022 | [email protected] | ... |
Once the emails are added to the Google Sheet, I would like Google App Scripts to get the values of column B (containing the emails), and add them as an editor.
Questions
- What is the best way to set this trigger? OnChange...when the form data is received, a timer, or something else.
- How do I get the script below to create the correctly formatted array so it does not throw the error.
Expected Array Format
const emails = [
"[email protected]",
"[email protected]",
"[email protected]",
];
Current Formula With Error
Exception: The parameters (number[]) don't match the method signature for SpreadsheetApp.Spreadsheet.addEditor.
function addEditAccess() {
const SS = SpreadsheetApp.getActiveSpreadsheet();
const sheet = SpreadsheetApp.getActive().getSheetByName("Emails");
const emails = sheet.getRange(2,2, sheet.getLastRow() - 1, 1).getValues();
SS.addEditor(emails);
}
Solution 1:[1]
On the google form, open up script editor, and add the following code. Then set the trigger to: Use onFormSubmit
function addEditAbility() {
const SS = SpreadsheetApp.getActiveSpreadsheet();
const sheet = SpreadsheetApp.getActive().getSheetByName("Emails");
const emails = sheet.getRange(2,2, sheet.getLastRow() - 1, 1).getValues();
SS.addEditors(emails);
}
@Cooper @Liquidkat for the help with the trigger solution.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | JamesReed68 |
