Pfthb

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?

re:

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

Maybe:

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

For example:

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.

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.

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?