Google Sheets blank for new visitors?

2
votes

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:

  • one formulas require the sum of differences between two columns [(C2-B2)+(C3-B3)+C4-B4)…] and this sum must be divided by 7. I could not find a way to implement this calculation in one cell;

  • if the sheet is accessed by an user the editable area should be blank so that the user enters his data without having to erase first the data entered by a previous user.

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

3
google-sheets
can you please explain more about your issue with code or errors. - Ashish Kamble
Here is the sheet I am working on: docs.google.com/spreadsheets/d/… - range B2:H8 should be blank (no numbers in the cells) when somebody visits the sheet. I do not know how to do it! - for the second issue I have to sum up the difference between two columns, =SUM(C2-B2)+(C3-B3)+(C4-B4)+(C5-B5)+(C6:B6)+(C7-B7)+(C8-B8), then divide the result by 7 (because there are 7 rows) and display the result in one cell. Again, I do not know how to do it! Thank you! - Felix
@Felix I can see this can be an important spreadsheet. Are you conducting a survey? Do you plan to recover the data from the spreadsheets and aggregate it. - Tedinoz
Tedinoz - Not actually a survey and I do not plan (or know) to recover the data from the spreadsheet. This a spreadsheet that allows people with diabetes on self monitoring of the blood glucose (finger pricking) to calculate some indices to figure out the glycemia variability (and how stable their diabetes is) from their daily glycemia readings (at least seven readings per day). - Felix
@Felix Thank for clarifying the use of the spreadsheet - unfortunately it makes things more complicated. I am concerned about how to distribute your spreadsheet. It looks like you want your spreadsheet to be like a template but this is easier said than done. In addition, the spreadsheet is to be "available to any user" but later you refer to a "visitor" - you haven't defined what you understand by "user/visitor". For example, is a "user" only a person who has their own Google account, or is it anyone who can access the internet to share your spreadsheet? - Tedinoz

3 Answers

1
votes

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

1
votes

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.

0
votes

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?