sheet application for spray booth calculations.

[Neil Chakrabarty]

Hi Folks,

  We are moving from Excel to Google Sheets.

  We have an Excel file with code that we send to applicants for Spray booth permits.  The Applicant fills in the information requested in the file and returns the file in email.

  In Google sheets we are able to write the code so the sheet behaves the same way except for permissions for the code.

  We make a copy to send to applicant number 1. Then another copy for applicant number 2.  We test with internal staff and the applicants 1 and 2 both get permission questions they cannot get past.

  We wish to make this transition but are confused with this problem and we are new to this coding environment also.

  Do we have to use the deployment feature as if it were an addon?

  We hoped to make a link on a web page that would auto copy the spreadsheet into a folder and name it for the applicant then send them a link to the spreadsheet that is ready to use.

Thank you

 Neil Chakrabarty

[Alan Wells]

You could use a Google Form or an Apps Script Web App for the user input.

And instead of making lots of copies of spreadsheets, I'd create PDF files.

So right now you're using the Spreadsheet for the input form, the receipt, and data storage.

Use an input form for the input form, a PDF for the receipt and the spreadsheet to store the data.

If the Sheet is more than just an input form, but like an app, then a Google Form can't provide feedback depending upon user choices.

But a Web App can do that.

You can trigger your code to run from an:

On Form Submit Trigger

That trigger runs whenever the Google Sheet detects that it has received a new row from the Google Sheet.

If you don't want to create the PDF file, then you can omit that part, but still use the Google Form.

If you want more control, then you can develop a Web App for input.

You probably don't need to publish an add-on.

The user can use the Google Form without needing to copy any code or expose the code to users.

If there is an Apps Script project bound to the Sheet, and you make a copy of the Sheet, then the code gets copied also.

But, every copy of the code needs to be authorized.

It's not central code running from one place for different documents.

If your users must run code inside the Sheet, then maybe you'd want an add-on.

Are you using the Sheet to be a User Interface that provides feedback to the user?

[Neil Chakrabarty]

Hi aj.addons!

  Thank you for the reply.  Yes a web application is possible.  However I was hoping to keep it simple for our internal users.  The spreadsheet does several pages of calculations and the code simply summarizes the results. So the bulk of the work is the spreadsheet which our internal to adjust as they are chemical engineers.  For a web app I would have to make most of the changes for them.  They do covert to a PDF when done to freeze the results but that is not a problem.

So I was hoping to provide an analog to their existing process.

Thank you again.

 Neil Chakrabarty

[Alan Wells]

Trying to duplicate all the calculations from the Sheet in a Web App would be a lot of work,

but you could embed a read only Sheet in a Web App or Google Site.

Because of the permissions problem, you'll probably need an alternative to making copies of the Sheet and sending it to people.

Will the applicants always have a Google account?

[Neil Chakrabarty]

The applicants are similar to members of the public so some may and some may not.

I appreciate your thinking about this with me.

[Alan Wells]

Users who don't have a Google account, can't install an add-on.

So, that's not an option.

A Web App can run as you, and not need the user to have a Google account.

A Google Form doesn't need the user to have a Google account.

A Google Sheet can be shared so that anyone with the link can be an editor.

You wouldn't want multiple users editing the same Sheet at the same time and overwriting each others input.

If you had a Google Site or a Web App, then it could create a copy of the Sheet and embed that copy

into the current session for that user.

That way you wouldn't need to email them the Sheet.

When you make the copy of the Sheet, do you set the sharing permissions on the Sheets file?

[Neil Chakrabarty]

I suppose a manual way would be to put in a dummy script to execute so we can grant the permissions needed before emailing a link to the sheet. 

I will meet the internal users today and see what they want to do.  They may choose the manual path as then they change the calculations when ever they want instead of putting in a ticket for a developer to change it.