CODEWITHSUNDEEP
google-sheets
Felix
Felix

Reputation: 31

Google Sheets blank for new visitors?

I am trying to create an ordinary Google Sheets spreadsheet available to any user, with row/column headers, users enter their numerical data in columns in an editable area (B2:C8), and at the bottom of the columns there are several cells with formulas that use the numerical values entered by users to calculate a value. I am having two problems:

How can I make the editable area of the sheet blank for a visitor?

Upvotes: 2

Views: 394

Answers (3)

Tedinoz
Tedinoz

Reputation: 8044

Ensuring that the data entry area will be "blank for the next visitor" is an important consideration. The spreadsheet is to be used/accessed freely, anonymously with complete privacy, by an unknown number of people. The users do not necessarily share a common understanding about the sharing of spreadsheets, and some users may or may not immediately understand or comprehend the on-going implications of how to use, access and copy the spreadsheet. Ideally the spreadsheet could be distributed as a "template". But this is not an option for users of the free Google Docs service.

In short, it is unsafe to delegate the process of creating a copy of the spreadsheet. There's also the matter of protecting the questioner's integrity by ensuring that no end-user can ever accidentally access or delete the data of another person. For these reasons, the spreadsheet should never be directly accessed or opened by an end-user.

The solution is let Google create the copy of the spreadsheet. The "normal" shared spreadsheet url ends with /edit (even if a user has "View only" access). Manually substitute /copy for /edit to change the url like this:
Copy url -> https://docs.google.com/spreadsheets/d/1Gp4_TgazhYsZmJWEsRXYtEf2XJAW0sCrsS-o4ZN3oKw/copy
Edit url -> https://docs.google.com/spreadsheets/d/1Gp4_TgazhYsZmJWEsRXYtEf2XJAW0sCrsS-o4ZN3oKw/edit

When the url is accessed, the end-user will first see this screen. Note that the original document name ("Indici_diabetici") is shown.

Google copy document

By clicking "Make a Copy", Google creates a copy of the spreadsheet. The copy has a new ID, the user becomes its "owner" and it is saved in the user's own Drive account.

The original spreadsheet should be shared for "View only" access; this will ensure that if an inquisitive user were to change the url back to /edit they would be unable to change or modify the original spreadsheet.

The questioner would most likely include some appropriate explanatory information on the first sheet of the spreadsheet, so the end-user will still have guidance about the purpose and use of the spreadsheet.

To simplify access and avoid confusion, I would suggest that the actual url should be published only as a web link. Something along these lines: Want a copy of this template?

Upvotes: 0

Ashish Kamble
Ashish Kamble

Reputation: 2627

I can find solution for Sum and divide logic

=SUM(C2-B2,C3-B3,C4-B4,C5-B5,C6-B6,C7-B7,C8-B8)/7

Sum is -286 after Div by 7 will be -40.86

Yes there is a way to make your row clear but not for new visitor but for Time basis.
You can Try Automation script for that,
Read How do I create an Auto-Clear Script in a Google Spreadsheet?

1)Go to https://script.google.com and then add a New Script
2) Use I checked this Working for me,

function myFunction() {
      var sheetActive = SpreadsheetApp.openById("1Gp4_TgazhYsZmJWEsRXYtEf2XJAW0sCrsS-o4ZN3oKw").getSheetByName("Indici");
      sheetActive.getRange('B2:H2').clearContent();
}

3) Goto Run & select Run function and then select clearRange.
4) Goto Edit, Select 'current project triggers'
5) add trigger
6) time driven minutes every 5 minutes run this script automatically.
7) In All your triggers popup windows, select clearRange as the Run function.

Upvotes: 1

pnuts
pnuts

Reputation: 59485

re:

I could not find a way to implement this calculation in one cell;

Maybe:

=(SUM(C2:C)-SUM(B2:B))/7

For example:

SO53315931 example

Upvotes: 1

Related Questions