Family Expenses App. Glideapps + Google Script. Part 1 of 2

👋 Greetings!

I’m glad to share the app for family finances. Meet Finanso! Our family is happy using Finanso for 3 months so far.

Financo3

Finanso is powered by Glideapps.

Glideapps = App+Sheets

Glideapps ensures the data is synchronized between spreadsheets and the App. When I enter the data to my phone, I see it in Google Sheet and vice verse.

           GlideApps
 _______________^_______________ 
|                               |
📱 Mobile App ←·→ 📗 Google Sheet

Glideapps is a no-code constructor for mobile apps. It allows to create and develop a fully customizable mobile app:

Their beautiful web-interface lets change every little detail of the app, even the icon. I love it! 😍

Free App with Glideapps

Anyone can create as many free apps as she wants. The limit for free usage is 500 rows per app. 

Quiz:      Create a Free App 
Limit:     500 rows of data, according to Pricing
Growth:    My real data is growing with the speed ~4 rows a day
Time left: We have about 4 monthes to exceed the limit
Task:      Create a free app. How?

I thought of limiting the amount of data in the app. My needs were: daily expenses and the report. I wanted to copy my daily data to a separate spreadsheet. I did not know the real amount of data at the time and decided to clear the data daily. Happily, no need to copy and modify data manually, Google Script does it for me:

            GlideApps                         Google-Apps-Script
 _______________^_______________            _________^__________
|                               |          |                    |
📱Finanso App ←·→ 📗Finanso Sheet ←-----·→ 📗Fananso Backup Sheet

Every night the Script does his job. The script reads the data we entered, converts it into tabular form, copies it to the Backup file, clears entered data, and creates the report.

The limit was 500 rows, I’ve done with 24:

Price

My Finanso app

I’ve spent about 5 hours on developing Finanso. You may see the result and create your own copy, of course (see how in the next section)!

Finanso has two separate tabs for my wife (Lu) and me (Max):

img-share

Here’s a sample usage of Finanso. You spend $500 and need to enter the expense. Your actions are:

  1. go to your tab: ( Max for me, but could be your name),
  2. tap on category: Gifts,
  3. fill in Sum (500) and Comment (For Mum)
  4. You’re done ✔️

There are 5 slots for Sums and Comments. We’re usually alright with 1-3 entries per category, so 5 must be enough.

You’ll need to enter every expense into Finanso. As a reward, you’ll have a report like this:

The report!

The report!

The report adjusts every 24 hours. It is always up-to-date, showing the last 30 days stats. The expenses are sorted by sum. The total is shown at the top. Each row is clickable.

You’ll also have the backup — the Spreadsheet with all your data:

Separate data table: my Backup Google Sheet

Separate data table: my Backup Spreadsheet

Install Finanso

Installation is not simple yet, I’m working on it 👷‍♂️…  If you’ we read so far, then you may be still interested in your own Finanso app (I hope so). Please follow these 2 steps to setup:

  1. Copy the app & linked file.
  2. Copy the backup file and script and create a trigger

Step 1. Copy Finanso App

To get the app, follow this link on your PC:

https://finanso.glideapp.io

(be sure to open the PC version)
You’ll see the page like this:
fin_load
↑ Click COPY THIS APP button $)
You’ll need to create a new Glideapps account.

When you copy the app, you’ll be redirected to your GlideApps editor:

↑ The place where you create and modify the app.

↑ The place where you create and modify the app.

You’ll see the newly created app, your copy of Finanso. This copy will belong to your account, and no one else will have access yet.

You may then easily modify and share it. Please see the guide here: https://www.glideapps.com/learn-to-glide

One more thing, you’ll also have a copy of the linked Google Spreadsheet. Please open it via editor:

glide-instal3

Click Edit sheet in the left panel of the editor to see the file. This will open the spreadsheet in a new tab:

Linked Spreadsheet

Linked Spreadsheet

↑ This file will be also a part of your Google account, so you could also find it in your Google Drive. Please copy the ID of this file, we’ll need it later. To copy the id, go to the browser URL:

glide-inst2al3

You’ll need this ID in step 2.

Step 2. Copy the backup file and launch the code

To get the backup file, follow the link:

Copy Finanso Backup (Spreadsheet + Code)

As you start using the app, please delete the data from sheets: \Data/ and \Dates/. These sheets contain sample data, and you’re going to create your own!

clear old data

↑ The Backup file contains dummy data. Please delete it.

Now you’ll need the ID from Step1. It was an ID of the linked file. Paste this ID to the sheet \_Jobs_/ to the cell B2:

Sheet called "_Jobs_"

Sheet called “_Jobs_”

Now the script is ready to go. You’ll need to launch the script to run on a daily basis. Go to menu Tools > Script Editor to see the code (or use a keyboard [Alt → T → E] in Chrome).

glide-install

↑ A new tab with the code

The code is here, but it does nothing yet. You’ll need to create a trigger. Click on the timer icon (1) and create a new trigger (➕).  The function of a trigger is called test_Jobs (2). Please use these settings:

glide-install2

Your own app is ready now! The bad news is that your copy is currently running on the same tabs as mine. You may ensure it if you go to your linked Spreadsheet, you’ll see 2 tabs: Lu and Max. You may also notice that the app has the same 2 tabs: Lu and Max.

glide-ifnst2a22l3

I assumed that your family needs the same 2 tabs and you’re ok to name them Lu and Max. It is not true of course! I also assumed that your expenses have the same categories: Food, Life, Party, Gifts, Health, Clothes, Rent, and Other. This may be true, but I doubt it.

Let’s fix this inconvenience and modify Finanso to your needs. I’ll cover the engine details and modification points in Part 2.

 

Cascade Dependant drop-down lists

Hello!

One of the most needed features of tables is creating dependent drop-down lists, like these:

cdl

List 1 is the main for list 2. List 2 is the main for lists 3 and 4. I’ve already supposed one possible solution. And here’s why I suggest you this one, too.

Problems solved:

  1. Limit on 500 items for the list is not the case now: cascade lists are based on a range, not an array.
  2. The speed of the script is improved.
  3. Settings of lists are easier: the code of settings is created by the formula.

The main challenge was: speed. Here’re 2 versions of the same script in comparison:

V0: reading data from the sheet. 
V1: reading data from script memory

speed-up

The result: 4 times faster!

Solution: 2 files:

  1. Main: create the reference and set the script (copy sample file and script)
  2. Work file: import the reference, use drop-down lists  (copy sample file and script).

Install Cascade lists

Create a library:

Main File 
    Menu
       Tools > Script Editor 
               (in new tab)...
                    Menu 
                       File > Manage Versions > Save new version

Save library key:

Main File 
    Menu
       Tools > Script Editor 
               (in new tab)...
                    Menu 
                        File > Project properties > Copy Project key

You’ll get a key like this:  M7_AcDUdXsYoZmAeXcXharSwhNvePg-OV

Install the library:

Work File 
    Menu
       Tools > Script Editor 
               (in new tab)...
                    Menu 
                        Resources > Libraries > Paste Project key

cddl_library+install

Set Cascade lists

The script of the work file has settings in it. All these settings may be created on the sheet “Script” of the main file. Sample settings for 1 dependency are as follows:

// [1];
obj_dv.sheets = ['Expences'];
obj_dv.dataRangeA1 = "'Imp_Database'!C4:K14";
obj_dv.rowStart = 3;
obj_dv.colStart = 2;
obj_dv.colEnd = 6;
obj_dv.sheetRefName = 'Imp_Database';
obj_dv.refColStart = 3;
obj_dv.refRowStart = 4;
obj_dv.refCols = 9;
obj_dv.refRows = 11;
var res2 = runValidation2d_(e, obj_dv);
Logger.log(res2);

You set only yellow cells:

cdlsets

When you enter the yellow cells, copy the generated code and paste it to the script of the Work file.


That’s the minimum info needed to install the script. Please post your questions on the comments. I’ll be back to add more details to this post.

Thank you!

Generate me, Jobs

How to create 30+ Google Sheet files with dummy data using Google Apps Script.

If you need 30 or more files with dummy data, with different numbers of rows, please see this solution. Think of your own set of files for tests, like this:

  1. Import a big amount of sheets with IMPORTRANGE formula
  2. Import a big amount of sheets with Google-Apps-Script
  3. See what method gives the best performance.

If you want to create your own dummy files, please follow these 3 steps:

Step 1. Copy the file ↓

Generate me, Jobs (copy)

Step 2. Fill the yellow cells ↓

ag1

Note: if you need more than 30 files, please launch the script by parties of 30 files.

Step 3. Launch the script ↓

ag2

The result:

ag3

Each dummy file has the same structure:ag4

It has a number of rows between upper and lower bounds (you set them). In my case, the file generated 400 rows, the number between 100 and 500.

It has an Id of each row, column Key.

It has some random numeric values.

It has also a column with dates, so you could test dates too.

It has the link and the name of the file in case you’ll need to check the source file.

The data of dummy files is recalculated by the formula.


Credits:

 

SheetSpam. My new video series

You’ll soon see a lot of new super-short videos on my YouTube Google-Spreadsheets blog. I called them SheetSpam for these reasons:

  1. My videos are short (less than a minute)
  2. Each video provides a single idea.
  3. Videos are silent: they have no voice‐over.
  4. You’ll be able to see about 25 new videos per month. This is spam =)

The goal is to popularize Google Sheets and share ideas. You’ll learn special functions and how powerful Google Sheets can be. There are a lot more that I want to tell than I can. This is a reason for SheetSpam. Each video will show:

The possibilities of Google Sheets are amazing!

SheetSpam videos are not so good as educational micro-video. I do not care about timing, my only rule is to make the video length less than a minute. I don’t spare time on talking and editing voice. I don’t spare time on the description below each video. I have an opportunity to express myself, and hopefully to show everything I wanted to show.

Less time on talking, more time on ideas.

I hope you’ll like the format. This is an experiment for me, it was risky to produce them before testing and seeing the reaction.

The first party of SheetSpam came to the channel on the 12 September, 19.

Join Sheets, Jobs

This article is an answer to the comment by Luis Paliotes:

Hello

I was wondering, If it’s possible to build a Google Script that performs the same action as the following query:

=query({IMPORTRANGE("SpreadsheetKey1","SheetName!A2:M");
        IMPORTRANGE("SpreadsheetKey2","SheetName!A2:M")},
  "SELECT Col1,Col6,Col4,Col5,Col8 WHERE Col5='Not Treated'")

The problem I’m facing is that I am using this Query to connect 16 different Spreadsheets, and this doesn’t work on a robust way as due to the amount of data google turns it on and of.

My answer will relate to Jobs and Ala-Sql.

I’ve created a test file with 5 different sheets to join:

js1

The problem: how to join data from 5 sheets with a condition, and put the resulting array into sheet Master.

Here are the final Jobs settings:

js2

Please learn more about Jobs if you’re not familiar with it.

The data sample from sheet S1:

js3

The resulting data in the sheet Master:

js4

Notes about the execution:

Step 1. Get the data from sheets

Fill the task id, file id, and other elements to remember initial ranges:

js5

Note the names from column Option1: it is the names of holders. Google Script will remember the data and put it into an object. The holder serves as a key to this object. You’ll need to set individual names for each piece of data in Option1.

Step 2. Join each piece of data into a single array

The query is:

select * from ? where Col1 != '' 
union all corresponding 
select * from ? where Col1 != '' 
union all corresponding 
select * from ? where Col1 != '' 
union all corresponding 
select * from ? where Col1 != '' 
union all corresponding 
select * from ? where Col1 != ''

js6

This technique is illustrated in Ala-Sql-Sheets Samples here.

Note the Option1: it is the ​ string s1~s2~s3~s4~s5 matching all 5 holders from step 1.

Step 3. Filter the result

Use SQL-query like this:

select * from ? where Col5 = 'Not Treated'

I passed the result into the holder filtered:

js7

Step 4. Refresh the result

js8

Note that the formula refreshes the result automatically. I’ve added the task to clear old values before the insertion. This will ensure that the script acts as a formula.


 

That’s it!

You may copy and test:

Happy coding!

 

 

 

Manager, Accountant, and Google Sheets

The case. Manager and Accountant

Here’s the Manager:

photo-1485712207830-8a665e701494

Photo by Daria Nepriakhina on Unsplash

She uses the phone to share photos on Facebook edit her Google Sheet with a purchases report.

Here’s the Accountant:

photo-1461988625982-7e46a099bf4f

Photo by Crew on Unsplash

He’s chatting with his girlfriend editing the main purchases report in his Google Spreadsheet.

The Manager and the Accountant use different Google Spreadsheet files. Their sheets share the same information: products, dates, prices, checks. They still don’t use the same file. There are some reasons to use different files. Differences are as follows:

  1. The manager has a small list of items, being purchased this week. The manager sees only her orders. She has a limited number of columns to see and change on the screen. The manager needs a tool to quickly change data about her items. She uses a phone, not a computer.
  2. The accountant has a full list of items. The accountant sees orders of all 5 managers and also other stuff in his Spreadsheet. He has access to all the columns and calculations. The accountant needs to see the fresh info from managers. He uses the computer to deal with all this stuff.

The problem. How to develop the connection between the Manager and the Accountant in Google Sheets

The accountant owns the main table. Will call it Main.
The manager owns the partial table. Will call it Part.

Imagine more than one manager in this system. Each manager owns her own file. This means we need to establish multiple connections:

One Main table may be connected with multiple Parts.

The real challenge is: how to remain the system up to date. How to connect Parts to Main in a way that both: the manager and the accountant are happy. Do we need to copy the data manually, use formulas, or develop the script?

The solution. Use Jobs to create connections between sheets. Make the Manager and the Accountant happy

The solution uses Jobs. If you’re not familiar with Jobs, think of it as a script full of preset standard tasks. Copy Jobs and try it in action. Each line of settings in Jobs is a single task: remember values, modify values, paste values.

jobs-lines

Jobs will accomplish all the scripting part. Let the program dealing with all the programming for you.

You’ll need to create a scenario or an algorithm. The algorithm is a list of tasks for Jobs, that you set in a tabular form (see the picture above). Here’s how it looks like:

  1. Task #1. Update Main for the Accountant
    1. Remember Part1
    2. Remember Part2
    3. Join Part1 + Part2, use a query: select * from ? where Col1 <> ''
      union all
      corresponding select * from ? where Col1 <> ''
       . Will produce the new data, call it Parts. Note: Jobs uses Ala-SQL to implement SQL requests.
    4. Join Main with Parts, use a query: select Main.Col1, coalesce(Parts.Col2, Main.Col2) label, Main.Col3 date, Main.Col4 mark from ? Main outer join ? Parts on Main.Col1 = Parts.Col1. Will produce the new data, call it “New-Main”.
    5. Replace the date from the Accountant’s table with the “New-Main”. Done!
  2. Task #2. Update Part for the Manager

…and so on. I’ll describe this method in detail and provide sample files later. Please star this post and write the comment if you’re interested in this topic, to let me continue the article.

Thanks, and see you!

Query me with SQL, Jobs

This article is related to Jobs.

Good news! I’ve added the new functions to Jobs:

runPureAlaSql_
runCol1AlaSql_

You now may query your data with SQL-like language:

SELECT 
  data1.Col1 col000, 
  data1.Col2 col001, 
  data1.Col3 col002, 
  data1.Col1 col010, 
  data2.Col2 col011 
FROM ? AS data1 JOIN ? AS data2 
  ON data1.Col1 = data2.Col1 
  order by col002 desc

Here’s the full list of functions available:

https://github.com/Max-Makhrov/sheetjobs#Jobs-Functions

Cheers!

Upload image from your computer to Google Drive using HTML-form & Show image via URL (IMAGE function) in your Spreadsheet

Problem

The user needs to show pictures: list products or see product design on the screen. The new Google Sheets feature — insert an image into a cell — works fine. The drawback of this method is that an image is actually saved into the file’s body:

  • you can’t copy this image to the other file without copying the entire sheet.
  • you risk slowing the performance of the file containing lots of images.

Solution

Save image on Google Drive and show it with the help of IMAGE formula.

I’ve managed to create a sample script that opens the HTML-form, showing images from your computer, so you could choose one and load it into Drive, and show it in a cell.

Please see the code here.

The whole trick works from Google-Apps-Script. This gives us new possibilities on user interactions. See how it does:

One cell is used to store the IMAGE formula.

img_1

The event or button in a custom menu launches the script.

img_2

The user is prompted to select an image.

img_3

The image loading is shown in an HTML-form.

img_4

When the user submits the form, the image is shown in a sheet.

img_5

The actual image is saved to the Drive folder.

img_6

 

 

 

 

 

 

Google Sheets and Regular Expressions

Please meet my personal list of regex tricks:

make a copy and see the formulas

I love using them. It worth learning regular expressions because of their power. One short smart expression may substitute a hundred lines of code.  It is my timesaver.

You may join the game! Share your favorite tricks with regular expressions. Post them in the comments, and I’ll fill my list with new knowledge. Wise men say:

There is no knowledge that is not power

and

May the force be with you!

Photo by Ian Stauffer on Unsplash

Photo by Ian Stauffer on Unsplash.