-1

I have set up a Google Apps Script that validates data that is entered into various Google Sheets files. I am looking for a way to allow users to access those Google Sheets files without making them sign in manually.

There are various onEdit() triggers set up that monitor these Sheets files and apply formatting to cells when data input errors occur. I'm doing validation with Google Apps Scripts because the errors are determined through non-straightforward business logic which makes Sheets' built-in data validation insufficient. The triggers are set up by a standalone Google Apps Script project. This setup means I cannot simply share a link to the Google Sheets file and have people edit it anonymously and have the data checked and cells formatted, because the scripts require more permissions than are necessary for anonymous editing.

I've looked briefly at some Google Auth API docs, but I'm not a security or web dev by trade so it's a bit confusing.

I'm looking for a solution where the user could click on a link to a Sheets file and automatically be redirected to the Sheets file and be signed in to a Google account that is managed by me, and which already has permission to access that Sheets file (this way they won't be editing these files with their personal accounts). The link would be on a Drupal site that users would have already signed into using Drupal's built-in sign-in functionality, in case that makes a difference.

player0
  • 124,011
  • 12
  • 67
  • 124
gccree
  • 63
  • 2
  • 7
  • It's an interesting question, but because you haven't included any code and are asking for general advice, it's "off-topic" to Stack Overflow. You might try posting the the [Google Apps Script Community](https://groups.google.com/forum/#!forum/google-apps-script-community) – Alan Wells Aug 08 '19 at 15:42
  • @AlanWells Thanks for the advice. I've posted a question there: https://groups.google.com/forum/#!topic/google-apps-script-community/TxJ4zBsyD_Y – gccree Aug 08 '19 at 15:51
  • Good. I saw the post. I don't have the exact answer. I might have some ideas, but they are untested and unverified. – Alan Wells Aug 08 '19 at 19:45
  • @AlanWells I'm happy to test any ideas; my real issue is coming up with them since I'm not very familiar with the world of authentication and its possibilities – gccree Aug 08 '19 at 20:08

1 Answers1

1

You can write a stand-alone script and build a trigger yourself:

ScriptApp.newTrigger('Edit')
  .forSpreadsheet('THE_SPREADSHEET_ID')
  .onEdit()
  .create();

function Edit()
{  
//does not necessary need to be the same spreadsheet
 SpreadsheetApp.openById('THE_SPREADSHEET_ID').getActiveSheet().getRange('A20').setValue('it worked');   
}

If you set the sharing permission for the spreadsheet to "Anyone with the link can edit", every user (including anonymous) editing the spreadsheet will cause the trigger to run. Just make sure that after writing the script you run it once manually, after this it will run automatically on every edit.

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • That is currently how I'm building triggers. The part that I didn't realize was that I could allow anonymous edits in the Sharing menu on Google Sheets. Interestingly enough, the triggers fire and work, but toast messages don't show up and executions don't get tracked in the dashboard after anonymous edits. This still works for my case though. Thank you! – gccree Aug 09 '19 at 13:27
  • If you go to https://script.google.com/home -> `'NAME OF YOUR YOUR PROJECT'->Project Details->Executions` - the executions don't get tracked? When you say "toast messages don't show" - are you implementing them as `SpreadsheetApp.getActiveSpreadsheet().toast()`? Try `SpreadsheetApp.openById('INSERT SHEET ID').toast()` – ziganotschka Aug 09 '19 at 13:40
  • The executions are in fact showing up there; I was mistakenly looking under 'My Executions'. I tried the toast popup via openById but it still doesn't show up for the anonymous editor. – gccree Aug 09 '19 at 19:59
  • Hard to say where the problem comes from without seeing your code. – ziganotschka Aug 09 '19 at 22:47
  • I posted it as a separate question here: https://stackoverflow.com/questions/57480181/google-apps-script-toast-messages-dont-appear-for-anonymous-editors – gccree Aug 13 '19 at 16:06