Last Updated on May 9, 2023 by mishou
I. Google Sheets tips
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:
- https://docs.google.com/spreadsheets/d/1_zCHc8AvtePqE9sJUiQ4QkUgApN-Vmjoc_bBv-M6f5U/edit?usp=sharing
- https://docs.google.com/spreadsheets/d/1qWETi1xsENFiQkAcCpX79mX7LmejeRhRYELZQ-_JjWQ/edit?usp=sharing
II. Reading data on a webpage
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 like this:
You have to copy the table and paste it with values only, if you wan to 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)I have created a bar chart with the data:
III. Translation
You can translate an English word into a Japanese word with the following formula:
=GOOGLETRANSLATE(A2,"en","ja")
You can see Two-Letter Language Codes here:
https://sites.google.com/site/opti365/translate_codes
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
You can convert dollars at A1 to yen with the following formula:
=A1*GOOGLEFINANCE("Currency:USDJPY")
VI. Stock market prices
You can learn GOOGLEFINACE()from here:
How to use the GOOGLEFINANCE function in Google Sheets
You can get the average of a week with the formula:
=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/