[Google Sheet] How to download Google Sheet via command line as Excel file?

Requirement

How to download Google Sheet via command line as Excel file into specific directory?

How-To

In Windows:

powershell -command "& { (New-Object Net.WebClient).DownloadFile('https://docs.google.com/spreadsheets/d/xxxxx/export?format=xlsx', 'src/main/resources/source/batchConfigFile.xlsx') }"

In Mac OS:

curl -L "https://docs.google.com/spreadsheets/d/xxxxx/export?format=xlsx" > src/main/resources/source/batchConfigFile.xlsx

[Google Sheet] How to join double quote

How-To

Step 1. Check double quote value in ASCII Codes Table, i.e. char(34)

Step 2. Then you can using char(34) as bellows:

=concatenate(char(34), A1, char(34))

Reference

[1] https://infoinspired.com/google-docs/spreadsheet/join-double-quotes-with-text-in-google-sheets/

[Google Sheet] SUMPRODUCT function

Requirement
我想計算我所訂購的飲料總價 (i.e. I11)，有無可用的現成 function 快速算出

How-To
使用 SUMPRODUCT 此 function 可以完成此需求，此 function 有兩個參數，其會針對兩個大小相同的陣列或範圍，計算彼此對應項目的乘積總和。

[Google Spreadsheet] How to realize column alias in query function

Here has raw data

If I would like to find Apple's data, and rename column as bellows:

You can use label to do alias

[Google Spreadsheet] How to remove duplicate item?

Here has my raw data:

If I would like to know all distinct item as bellow:

We can make good use of unique function to remove duplicate items:

[Google Sheet] Add-ons : Size My Sheet

Problem
Each Google Sheet can support up to 5 million cells. If I reach its limitation, I cannot keep working on current Google Sheet.

How can I know the usage status in current Google Sheet?

How-To
You can get Size My Sheet add-ons in Google Sheet, then you can get the usage information.

Add-ons => get add-ons

Add-ons => Size My Sheet => Show gauge

[Google Sheet] How to get the last non-empty value from a row?

Scenario
I have a history data regarding selling quantity from January to May.
Assume the latest data is April, how do I get the last non-empty value from a row?

How-To
We can use index and counta function to fulfill this requirement.
counta is used to get the index value of non-empty cell index.
Then pass the value which get from counta to index function to get the last value.

[Google Sheet] How to provide dynamic parameter value for where condition in query function?

Here has raw data

If I would like to find specific fruit based on the input value:

You can do this way:

[Google Sheet] OpenSolver 使用範例

假設我有個生產決策必須決定，我要生產幾個 duck or fish 娃娃，放到娃娃機，並能讓獲利最大化。duck 與 fish 的每單位耗用橡膠量分別為 100 與 125，每單位利潤分別為 $5 與 $4

已知限制條件如下，依據歷史資料， duck 與 fish 每個月最大銷售量分別為 400 與 300個，每個月可用來生產的橡膠量只有 50000 g

打開 OpenSolver，設定目標利潤最大化

設定 variable cells

設定限制條件
(1) 生產的橡膠量必須 <= 每月可用橡膠量
(2) duck 生產數量必須 <= 每月最大銷售量
(3) fish  生產數量必須 <= 每月最大銷售量

按下 Solve Model 即可得到最佳解：在已知的條件限制下，duck 與 fish 分別生產 400 與 80 個，可以得到利潤最大化

[Google Spreadsheet] How to create pivot table?

Raw data

Data => Create pivot table

Configuration:
* Set Fruit as row
* Create multiple values, including summary, average and median.

Check Result

[Google Spreadsheet] How to realize group by function in Google Spreadsheet?

Assume I have the following raw data:

We can make good use of query function to meet group by requirement:

Check the result:

Reference
[1] https://webapps.stackexchange.com/questions/36103/how-to-group-data-in-a-google-spreadsheet

[GoogleDoc] Using max function in conditional formatting

Problem
Assume I have a number of row, I would like to highlight the max value. How to do it?

How-To
Steps are:
1. Select the range of data => Format => Conditional formatting...

2. Configure conditional formatting rules

3. Check result

Reference
[1] https://webapps.stackexchange.com/questions/100381/using-max-in-conditional-formatting-only-evaluates-numbers-after-the-cell-regar