Lecture: Google Sheets Tips ver. 4

Last Updated on July 9, 2022 by shibatau

The categories are changed: GoogleSheets to Lecture.

I. Google Sheets tips and tricks

You can learn Google Sheets tips here:

40 Advanced Google Sheets Tips for Marketing Pros

I have created some sheets using the tips above and add some others here:

  1. https://docs.google.com/spreadsheets/d/1_zCHc8AvtePqE9sJUiQ4QkUgApN-Vmjoc_bBv-M6f5U/edit?usp=sharing
  2. https://docs.google.com/spreadsheets/d/1qWETi1xsENFiQkAcCpX79mX7LmejeRhRYELZQ-_JjWQ/edit?usp=sharing

II. Reading data on webpages

1.Raw CSV data

Let’s read the raw CSV data that I have made here:


You can load the data with the formula:


You can create a pie chart with the data easily:

You have to copy the table and paste it with values only, if you change any values on the table.

2.HTML tables

WikipediaのList of countries by military expendituresのページの2番目のテーブルを読み込むには、A1に次の式を書きます。


You can create a pie chart with the data easily:

III. Translation




IV. Dictionaries and Wikipedia

1.Cambridge English dictionary (function)

Put English words in A2 and the following formula in B2

=IFERROR(JOIN(" ",INDEX(IMPORTXML("http://dictionary.cambridge.org/dictionary/english/"&A2,"//div[contains(@class,'ddef_d')]"),1)))

You will get the meanings like this:

2.Dictionary Functions (Add-ons)

Install an add-on called Dictionary Functions and put the formula like this:


You will get the meanings like this:

3. Wikipedia search bar (Add-ons)

Install an add-on called Wikipedia search bar and start the search bar:

You can search items and get the results on the side bar like this:

V. Convert currencies




How to create a Euro-Dollar converter with Google Sheets

VI. Stock market prices


How to use the GOOGLEFINANCE function in Google Sheets



VII. Add-ons



VIII. Creating functions with JavaScript


About shibatau

I was born and grown up in Kyoto. I studied western philosophy at the University and specialized in analytic philosophy, especially Ludwig Wittgenstein at the postgraduate school. I'm interested in new technology, especially machine learning and have been learning R language for two years and began to learn Python last summer. Listening toParamore, Sia, Amazarashi and MIyuki Nakajima. Favorite movies I've recently seen: "FREEHELD". Favorite actors and actresses: Anthony Hopkins, Denzel Washington, Ellen Page, Meryl Streep, Mia Wasikowska and Robert DeNiro. Favorite books: Fyodor Mikhailovich Dostoyevsky, "The Karamazov Brothers", Shinran, "Lamentations of Divergences". Favorite phrase: Salvation by Faith. Twitter: @shibatau

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.