How can I build a secure Spreadsheet/Google Drive database for a small business?

Here is a first step for you ...

Create a shared Google Sheet with three days' data that you would keep.
Do it all manually, and show it to us.
The shape of the data (rows and columns) is important to understand before coding.

Sir should i fill the coloum and rows with the data should be saved sir this is a sample can you say should i send like this, fill the columns with the topics??

Your first sheet shows that you are able to add a heading, but not yet at the point of being able to add numbers and assign them to rows and columns.

Here is an experimental sheet design for a pizza store for you to study.


Just examine the rows, columns, headings, and sheets, to see how they tie together.
Do not look at the blocks. sir here is the thing you asked
But i gave the formula for the today's bought as c1* 20+d1100+e1200+f150+g1150 and it should be changed as it should be multiplied like can number multiplied with can rate in shop details sheet and the same for all . Then after saving for one shop the balance(in shop details sheet)should be updated as the total(calculation sheet). If you look at he numbers you can find it.

And also an another thing when we should get the yesterday's balance from shop details while calculating

The next thing for you to study is the SUMIF and SUMIFS functions:

Also look at the =vlookup function, which can be used to look up prices by product in a separate price sheet.

Do not be afraid to add new sheets with formulas to take summaries of your details sheet by shop, by month, and by combinations of both.

Then look at the File->New->Form option on the detail Google sheet, to see how you can make a web form to help input new rows.


Your sheets are private, so I can't read them.
Use test data only, and mark them shareable in Google Sheets for others to see.

This is an exercise in modelling your data, so you need many eyes on your sample data.

I have some observations and questions based on your prior posts with sample data ...

  • It is unwise to put a column with balance data after the columns that count up sales of different products. It is inevitable that there will be a new product coming along, and that would force the balance column into a different column. Better to have it before the first product column and after the identifying columns (date, store). The same applies to the total value of the sale for that row. Move that nearer to the front of the row to avoid having to move it later.

  • Don't be afraid to add an opening balance column in addition to the closing balance and sales total columns, to allow new balance calculation capability based solely on the new detail row contents. The opening balance can be looked up by vlookup elsewhere at data entry time, from a separate sheet.

  • You base your balance exclusively on sales history. Isn't a balance normally based on sales history plus payments history?

  • It would be more in keeping with common business practice to track daily store balance in a separate sheet, if the payments are handled by a different person than the sales. Check with your Bookkeeper.

  • How much repeat business do you do with different stores? Are they stable enough to bother keeping their store names in a separate sheet just for the purpose of having a place you can copy them from when entering new detail data, for consistency and to enable you to filter and group by name later?