I have Sales Sheet that can be accessed only by me.
I have Sales Form Sheet (not Google Form, but In-Spreadsheet Form) that can input data to Sales Sheet through Google App Script and other users can access this Sales Form Sheet.
But in short, other users cannot input data to Sales Sheet. The error is "You are trying to edit a protected cell or object. bla bla bla..."
Is there any solution that can make other users input data to the only-me protected cell through script?
Thank you.
Yes, publish the script as a web app, with the options to run as me and set the access to anyone in your domain or anonymous.
In your script file create a doGet() function and call the script from within that function.
function doGet(e){
submitForm(); // This is your function that writes to the protected sheet
}
Going to the web app URL will then run the script as you and allow the user without premission to edit the sheet to submit the data as you.
Instead of placing a protection on the sheet, place a protection on the range that already has data, then expand that range with a time-driven function.
For example, assume 'Sales Sheet'!A1:Z10 have data, but all remaining rows are blank. You would protect only 'Sales Sheet'!A1:Z10. When someone submits new data, that data would go in row 11. Your time-driven function (maybe every minute) would then change the protection to include row 11 – 'Sales Sheet'!A1:Z11.
(Unfortunately, the current user can't be prevented from editing, so you can't trigger this action on submission.)
Your other option would be to have the data get submitted into a Google Form via Apps Script, save form results to a new spreadsheet, and then do an =IMPORTRANGE() so that other users can still see the submissions.
