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:

https://pastebin.com/raw/cSZ8pYWh

You can load the data with the formula:

=IMPORTDATA("https://pastebin.com/raw/cSZ8pYWh") 

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に次の式を書きます。

=importhtml("https://en.wikipedia.org/wiki/List_of_countries_by_military_expenditures","table",2)

You can create a pie chart with the data easily:

III. Translation

A2の英語を日本語に訳す式は次です。

=GOOGLETRANSLATE(A2,"en","ja")

中国語簡体は、”zh-CN”、ネパール語は”ne”、ベトナム語は”vi”、インドネシア語は”id”、ハングルは”ko-KR”です。

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:

=dict(A2,"en",{{"definitions","ipa"}})

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

1ドルが日本円でいくらかを算出する式は次です。大文字と小文字を区別します。

=GOOGLEFINACE("Currency:usdjpy") 

次にコンバーターを作成する方法が紹介されています。

How to create a Euro-Dollar converter with Google Sheets

VI. Stock market prices

次に解説されています。

How to use the GOOGLEFINANCE function in Google Sheets

Appleの終値の週毎の平均?を取得する式は次です。

=GOOGLEFINANCE("AAPL","Price",date(2020,1,1),date(2021,5,30),"Weekly") 

VII. Add-ons

例えば、Numbertext(無料)は簡単に数字を文字に変えててくれます。Add-onsのインストールは簡単です。次でインストールしてGoogleのアカウントで承認するだけです。

https://gsuite.google.com/u/0/marketplace/app/numbertext/505819167361?hl=en

VIII. Creating functions with JavaScript

http://www.mishou.be/2021/12/20/googlesheets-learning-custom-functions-javascript-ver-1/

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.