Google Spreasheets @max-makhrov - Tumblr Blog

My WordPress blog will migrate

Please see my new post on Medium: https://max-makhrov.medium.com/augh-again-service-using-too-much-computer-time-for-one-day-136d2779092b I will stop my WordPress blog for now because of ads it shows to users. I’ve decided to slowly migrate to Medium. There’s so much I can share, but his blog is on pause now. Thank you for reading!

View On WordPress

Google Sheets. Data Validation Rule now supports $$

Photo by Kaushik Panchal on Unsplash What has changed The data validation rules now have the ability to make relative links. If you copy a drop-down list with a link to a range, then the range will be changed according to the same rules as inside formulas. = $A$1 – the link does not change when copied. $ before a letter anchors the column, $ before a number anchors the string.= $A1 – when…

View On WordPress

#arrayformula #data validation #dependent lists #formulas

Timestamps

⏱️ Create automatic timestamps in Google Sheet

Summary

⏱️ Create automatic timestamps in Google Sheet:

unique IDs based on current time in milliseconds.

time of last update

time of first update

time of each update

Output timestamps in a cell, or in a note.

Install

COPY SAMPLE FILE to test it.

Change settings on sheet _onEditSets_

Go to menu Timistamp > Get the code!

Copy the code from the pop-up

Go to menu Tools > Script Editor...

View On WordPress

#Automation #sctipt #timestamp

Use Google Form as custom HTML-interface fro Sheets

The code is here: https://github.com/Max-Makhrov/google-form-to-sheet.gs

You can repeat my steps and embed Google Form to the sheet:

Installation:

Create a new Google Sheet, like mine

Create a new Google Form within the Sheet

Create a new script project

Create 2 files: code.gs and form.html and copy the code from this repo.

Edit first lines of the code: enter your form id and your sheet name…

View On WordPress

Merge and Unmerge Multiple Cells in Google Sheet

Looks like a beginners problem, but some solutions were not obvious for me.

Lets see 2 cases: (1) merge cells, (2) unmege (break apart) cells. I mean merge and unmerge multiple cells at once, with a few clicks, and without losing your data or formatting.

Merge cells Add-ons to merge cells

The add-on to merge cells by Ablebits called “Merge Values”. Interesting, there’s no alternative…

View On WordPress

#Automation #Google-Apps-Script

Web-Scraping. Official Holidays in Your Country to Google Sheet

Hello from 🇺🇦, Odessa! Here’s the LINK_TO_COPY this ↓

I’ve been working on calculating the work efficiency of a small team. They had their hours statistics, and needed a forecast on the future income based on the current work. This required knowledge of all official holidays. I mean real days-off when the person is officially not working.

Here’s an example:

The 9th of May — is Victory…

View On WordPress

#formulas #Web-Scrapping

Nested [+] report in Google Sheets with hiding details Tip: use pivot table + formulas to create a nested report: Project members inside a project.SAMPLE_TO_COPY

#arrayformula #conditianal formatting #formulas

SPARKLINE function. Sample Activity Pulse Mini-Charts

Pity, I did not find the way to create an array-formula for SPARKLINE in Google Sheets. Here’s my sample with multiple mini-charts:

PLEASE_COPY_SAMPLE

Thank you Sean O. from Unsplash!

Useful articles by this topic:

Big article with samples by Ben Collins — with downloadable sample for deep drive.

Documentation — not so big, but useful.

View On WordPress

#Charts #formulas

Jobs got new Interface. Select tasks from sidebar

Jobs is a program in Google Sheets to automate your tasks. Please copy Jobs to try the new interface.

View On WordPress

#sheetjobs

Script Magic. Create HTML-form with list from Sheet & search, like Google.

If you need to create list like this:

Please copy this sample file. And see my Git-Hub Repo for details.

The list (1) works from custom menu, (2) contains values from your sheet, or any values, (3) supports single or multiple selections, (4) supports search by letters, (5) returns selected values to the script when the “Submit” button is clicked.

View On WordPress

#Google-Apps-Script #html-forms #script

Get All Your Existing WordPress Posts to Google Sheets. No-Code Solution

I've got this table — list of all my WordPress posts — using formulas only 😀

I’ve got this table — list of all my WordPress posts — using formulas only 😀.

Here’s my sample Spreadsheet to COPY.

The process was as follows:

Are you…

View On WordPress

#arrayformula #formulas #WordPress

WordPress to Google Sheets. Pull New Posts into Your Sheet

👨‍🎓 Hi! I've managed to use Web-Hook to put my new WordPress posts into Spreadsheet.

👨‍🎓 Hi! I’ve managed to use Web-Hook to put my new WordPress posts into Spreadsheet.

Here’s how it looks:

One new row is added when I publish or update a post on WordPress

My Sample file is here: COPY.

Here’s how to steps:

Create new Google Sheets file and the code inside.

Publish an App

Create a Web-Hook with WordPress

All steps will take ~5 minutes.

Step 1. The code

The code…

View On WordPress

#Google-Apps-Script #optimisation #script #WordPress

No-Script Multi-line Dependent Drop-Downs in Google Sheets

Hi! 🙂 Glad to announce a way of creating Dependent Drop-Down Lists → DDLs in Google Sheets. No Script Needed!

Boom:

COPY-SAMPLE-FILE-LINK

Please take a look at sheet “Data”. The lists are dependent:

The last list only. Formula shows the correct tip only for the last entered row.

Non-strict. The formula allows user to enter the incorrect data, but showing correct options first.

Add new…

View On WordPress

#formulas #optimisation

Beginner: query formula for Bar Chart in Google Sheets

😇 Here’s the old tick I wanted to share. Copy Sample File!

You could create any Chart with a single QUERY formula or combination of QUERY+FILTER+SORT+UNIQUE

Here’s the chart:

The formula is:

=QUERY({A:C},"select Col1, sum(Col3) where Col2 <> '' group by Col1 pivot Col2"

{}— used to convert the range into the array. Now I can use Col1-notation for the formula and forget about the actual columns…

View On WordPress

#arrayformula #formulas #query

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

This is the sequel, the original post is here: Family Expenses App. Glideapps + Google Script. Part 1 of 2.

Modify Finanso.

Modification points are:

Create your own user tabs (or one tab if you’re single)

Modify categories for your expenses.

Lets’ shortly recall the scheme:

GlideApps Google-Apps-Script _______________^_______________ _________^_________ | | | | 📱Finanso App ←·→ 📗Finanso File…

View On WordPress

#forms #glideapps #Google-Apps-Script #optimisation #script #sheetjobs

Connect MySQL with Google Sheets

🙋 I’m happy to share this knowledge with you. It’s now a year since I’ve developed and used this engine in my project.

Photo by MI PHAM on Unsplash

Why MySql

MySql database is a powerful tool for business and works much faster than Google Sheets. MySql database easily stores and calculates millions of rows of data. You could get and update data by just typing simple SQL phrases like “select…” or…

View On WordPress

#import #optimisation #script #SQL

👋 Greetings!

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

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:

#gallery-0-9 { margin: auto; } #gallery-0-9 .gallery-item { float: left; margin-top: 10px; text-align: center; width: 33%; } #gallery-0-9 img { border: 2px solid #cfcfcf; } #gallery-0-9 .gallery-caption { margin-left: 0; } /* see gallery_shortcode() in wp-includes/media.php */

Easy to change the app

Easy to share

Easy to install

Easy to change all, even the icon

–

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:

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):

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

go to your tab: ( Max for me, but could be your name),

tap on category: Gifts,

fill in Sum (500) and Comment (For Mum)

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 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 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:

Copy the app & linked file.

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:

↑ 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.

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:

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

↑ 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:

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!

↑ 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_”

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).

↑ 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:

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.

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.

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.

#glideapps #Google-Apps-Script #optimisation #script

Trending Blogs

Recently Viewed Blogs

Google Spreasheets