Google Docs and Spreadsheets: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
m (Text replacement - ": Image:Owl icon.jpg " to "{{Tips}} ")
 
(14 intermediate revisions by the same user not shown)
Line 1: Line 1:
[https://drive.google.com/ Google drive]
[https://drive.google.com/ Google drive]


== search google drive ==
I used the #keyword-terms as #HashTag for better searching purpose. I found it's different between - & _ when naming the files. {{access | date = 2015-08-17}}


== 不同試算表方案的工作表或儲存格的大小限制 ==
case1: input keyword: {{kbd | key=<nowiki>aaa-bbb</nowiki>}} & return the results:
* {{Gd}} EXCEL 2007, EXCEL 2013:
* found the files which filename contains {{kbd | key=<nowiki>aaa-bbb</nowiki>}}
** (約100萬列) 1,048,576 列 * 16,384 欄 = 17,179,869,184 個儲存格<ref>[http://office.microsoft.com/zh-tw/excel-help/HP010073849.aspx Excel 的規格及限制] 工作表大小</ref><ref>[http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HA103980614.aspx Excel specifications and limits - Excel]</ref>
* [http://www.libreoffice.org/ LibreOffice]([http://portableapps.com/apps/office/libreoffice_portable/ LibreOffice Portable]) Calc 3.3.3版以後 (目前版本 4.3):
** (約100萬列) 1,048,576 列 * 1,024 欄 = 1,073,741,824 個儲存格<ref>[http://ask.libreoffice.org/en/question/8631/upper-limit-to-number-of-rows-in-calc/ upper limit to number of rows in Calc - Ask LibreOffice]</ref><ref>[http://www.libreoffice.org/get-help/documentation/ Documentation » LibreOffice] - [https://wiki.documentfoundation.org/images/b/b7/CG34-CalcGuideLO.pdf Calc Guide 3.4 (spreadsheets)]</ref> {{exclaim}} When I save the CSV as XLS format, I met the 65,536 rows limit & 256 column limit on linux LibreOffice 3.4.5 {{Linux}} /v. 4.2.3.3 {{Win}}
* [http://www.openoffice.org/ Apache OpenOffice] 3.x Calc:  
** (約60萬列) 65,536 列 * 1,024 欄 = 67,108,864 個儲存格<ref>[http://wiki.openoffice.org/wiki/Documentation/FAQ/Calc/Miscellaneous/What's_the_maximum_number_of_rows_and_cells_for_a_spreadsheet_file%3F What's the maximum number of rows and cells for a spreadsheet file? - Apache OpenOffice Wiki]</ref>
* EXCEL 2003: 工作表大小
** (約60萬列) 65,536 列 * 256 欄 = 16,777,216 個儲存格<ref>[http://office.microsoft.com/zh-tw/excel-help/HP005199291.aspx Excel 的規格及限制 - Excel - Office.com]</ref>
* Google spreadsheet:
** 約 1000列。 所有工作表共 40 萬個儲存格。如果只使用一個工作表，使用256 欄，則列數限制為 1,562 列<ref>[https://support.google.com/drive/answer/37603?hl=zh-Hant Google 文件、試算表和簡報的檔案大小限制 - 雲端硬碟說明]</ref>
* Office 365: [http://office.microsoft.com/zh-tw/excel-help/HA102837464.aspx 資料模型的規格與限制 - Excel - Office.com] 適用: Excel 2013, Power BI for Office 365 Preview, Power Pivot in Excel 2013


== Export MySQL query to Excel file ==
case2: input keyword: {{kbd | key=<nowiki>aaa_bbb</nowiki>}} & return the results:
EXCEL limit:  
* found the files which filename contains {{kbd | key=<nowiki>aaa_bbb</nowiki>}} & {{kbd | key=<nowiki>aaa-bbb</nowiki>}}  
* (1) "Total number of characters that a cell can contain: 32,767 characters" Using [http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php MySQL SUBSTRING() function] ex: {{kbd | key = <nowiki>SUBSTRING(string_column, 1, 32767)</nowiki>}} for EXCEL 97<ref>[https://support.microsoft.com/en-us/kb/296053 Summary of capability limitations in Excel 97]</ref>-2007<ref>[https://support.office.com/en-us/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa?CorrelationId=1de7e8d2-91e2-4eec-8868-4dc8cbd23463&ui=en-US&rs=en-US&ad=US Excel specifications and limits]</ref> ''unverified''
* (2) "Worksheet size: 1,048,576 rows": Using {{kbd | key =LIMIT}} for EXCEL 2007


== Import CSV to EXCEL ==
case3: input keyword: {{kbd | key=<nowiki>aaa bbb</nowiki>}} & return the results:
* [http://office.microsoft.com/zh-tw/excel-help/HP010099725.aspx 匯入或匯出文字檔案 - Excel] 支援 Unicode 編碼 {{Win}}
* found the files which filename contains {{kbd | key=<nowiki>aaa-bbb</nowiki>}} {{exclaim}} But NOT {{kbd | key=<nowiki>aaa_bbb</nowiki>}}
* 欄位內容分隔符號 " (雙引號); 欄位分隔符號 , 不可夾雜空白。Unicode 檔案需要檔案有 BOM，這樣才能點選兩下 CSV，Excel 開啟時沒有問題。 (Excel 2007) {{Win}}
* [http://errerrors.blogspot.tw/2014/05/mac-office-excel-csv-microsoft-excel.html Mac Office Excel 匯入中文 CSV 檔案 (Microsoft Excel for Mac 2011)] {{Mac}}
* 雖然 CSV 欄位已經用雙引號區隔，但是如果欄位裡面也有包含雙引號，匯入到 Excel，會導致欄位錯誤的問題。解法1: 原始資料的雙引號改成單引號、解法2: 不用 Excel ，改用 [http://www.libreoffice.org/discover/calc/ LibreOffice Calc]


== Copy & Paste ==
case4: input keyword: {{kbd | key=<nowiki>aaabbb</nowiki>}} (without space separated) & return the results:
{{#widget:Iframe
* found the files which filename contains {{kbd | key=<nowiki>aaa-bbb</nowiki>}} {{exclaim}} But NOT {{kbd | key=<nowiki>aaa_bbb</nowiki>}}
|url=https://docs.google.com/spreadsheet/pub?key=0AuFlnVY9TNrsdDJodlRYRG1zWUY4dFd4SktiZzVyYlE&single=true&gid=0&range=A1%3AH8&output=html
|width=100%
|height=AUTO
|border=1
}}
[https://docs.google.com/spreadsheet/ccc?key=0AuFlnVY9TNrsdDJodlRYRG1zWUY4dFd4SktiZzVyYlE&usp=sharing 線上編輯]


* Microsoft Excel --> Google Spreadsheet:
** columns & rows: Copy & Paste is ok
* Plain text --> Google Spreadsheet: ok
** columns: {{kbd | key = Tab}} separated columns --> Google Spreadsheet: ok
** rows: {{kbd | key = Enter}} separated rows --> Google Spreadsheet: ok
* Plain text --> Microsoft Excel: ok
** columns: {{kbd | key = Tab}} separated columns --> Microsoft Excel: ok
** rows: {{kbd | key = Enter}} separated rows --> Microsoft Excel: ok
* Microsoft Excel --> Table of Google Document:
** columns & rows: Copy & Paste is ok
* Plain text --> Table of Google Document: fail {{exclaim}} Suggest you copy to Microsoft Excel first and then copy paste to Google Document from Microsoft Excel.
** columns: {{kbd | key = Tab}} separated columns --> Table of Google Document: fail
** rows: {{kbd | key = Enter}} separated rows --> Table of Google Document: fail
* Plain text --> Microsoft Excel: ok
** columns: {{kbd | key = Tab}} separated columns --> Microsoft Excel: ok
** rows: {{kbd | key = Enter}} separated rows --> Microsoft Excel: ok
* [http://www.youtube.com/watch?v=Q43U-7r5AMk&desktop_uri=%2Fwatch%3Fv%3DQ43U-7r5AMk&nomobile=1 How-to Create and Copy a Table in Google Mail (Gmail) from Excel - YouTube]


== Google spreadsheet ==
{{Tips}} Walkaround: If it is too difficult to search the file from many files, You may register new gmail account. Share the specific project file or folder to the new account. It will become more easier and more efficient to search.
利用[[Google spreadsheet]]的函式，分析及統計可複選的問卷題目結果。


== Microsoft Excel ==
[[Microsoft Excel]]


== Upload Microsoft Word file to Google Docs ==
* {{exclaim}} When [https://support.google.com/drive/answer/2424368?co=GENIE.Platform%3DDesktop&hl=en Upload files and folders to Google Drive],  the layout of Word file after converted to Google Docs editor format may not the same with the original layout of Word file. In the [https://drive.google.com/drive/settings settings page] of Google Drive, do not select the option "Convert uploaded files to Google Docs editor format".


== Excel 「樞紐分析表(Pivot Tables)」==
== Presentation ==
區塊「Σ 值」的「計數 - 欄位名稱」(該欄位的項目個數)，除非是沒有填入任何資料的「空值」，否則都會被計算加 1。包含
Known the view mode Url of Presentation. Turn this into the downloadable Url if the owner of presentation file allowed. Sample HTML code as following:
* 邏輯值 {{kbd | key =TRUE}}、{{kbd | key =FALSE}}、字串、數值、錯誤代碼 {{kbd | key =<nowiki>#NAME?</nowiki>}} 等資料，會被列入計算加 1
<pre>
* {{kbd | key =<nowiki>IF</nowiki>}} 回傳的空值，也會被列入計算加 1 {{exclaim}} version: Excel 2013
<iframe src="https://docs.google.com/presentation/d/1JW8pAVOf7eHH6gm072etM2gLsQd8E5h5u75k44QuNCk/embed?start=false&amp;loop=false&amp;delayms=3000" frameborder="0" width="480" height="389" allowfullscreen="true" mozallowfullscreen="true" webkitallowfullscreen="true"></iframe>
</pre>
 
{long id} of this presentation file is 1JW8pAVOf7eHH6gm072etM2gLsQd8E5h5u75k44QuNCk
 
* View mode Url of Presentation ex:
*#<nowiki>https://docs.google.com/presentation/d/{long id}/pub?start=false&loop=false&delayms=60000</nowiki> or
*#<nowiki>https://docs.google.com/presentation/d/{long id}/embed</nowiki>
* downloadable Url (permission was required):
** <nowiki>https://docs.google.com/presentation/d/{long id}/export/pptx?id={long id}</nowiki>


== Post to MovableType from Google Docs ==
== Post to MovableType from Google Docs ==
Line 77: Line 53:
** Blog title: It's Case-sensitive and was not support Chinese.
** Blog title: It's Case-sensitive and was not support Chinese.


== references ==
== References ==
<references/>
 
<references />
 


[[Category:Blog]] [[Category:Google]]  
[[Category:Blog]] [[Category:Google]]  
[[Category:spreadsheet]] [[Category:Excel]]
[[Category:spreadsheet]] [[Category:Excel]]
[[Category:Data Science]]
[[Category:Data Science]]
[[Category:Search]]

Latest revision as of 20:23, 8 January 2025

Google drive

search google drive[edit]

I used the #keyword-terms as #HashTag for better searching purpose. I found it's different between - & _ when naming the files. [Last visited: 2015-08-17]

case1: input keyword: aaa-bbb & return the results:

  • found the files which filename contains aaa-bbb

case2: input keyword: aaa_bbb & return the results:

  • found the files which filename contains aaa_bbb & aaa-bbb

case3: input keyword: aaa bbb & return the results:

  • found the files which filename contains aaa-bbb Icon_exclaim.gif But NOT aaa_bbb

case4: input keyword: aaabbb (without space separated) & return the results:

  • found the files which filename contains aaa-bbb Icon_exclaim.gif But NOT aaa_bbb


Owl icon.jpg Walkaround: If it is too difficult to search the file from many files, You may register new gmail account. Share the specific project file or folder to the new account. It will become more easier and more efficient to search.

Microsoft Excel[edit]

Microsoft Excel

Upload Microsoft Word file to Google Docs[edit]

  • Icon_exclaim.gif When Upload files and folders to Google Drive, the layout of Word file after converted to Google Docs editor format may not the same with the original layout of Word file. In the settings page of Google Drive, do not select the option "Convert uploaded files to Google Docs editor format".

Presentation[edit]

Known the view mode Url of Presentation. Turn this into the downloadable Url if the owner of presentation file allowed. Sample HTML code as following: 

<iframe src="https://docs.google.com/presentation/d/1JW8pAVOf7eHH6gm072etM2gLsQd8E5h5u75k44QuNCk/embed?start=false&loop=false&delayms=3000" frameborder="0" width="480" height="389" allowfullscreen="true" mozallowfullscreen="true" webkitallowfullscreen="true"></iframe>

{long id} of this presentation file is 1JW8pAVOf7eHH6gm072etM2gLsQd8E5h5u75k44QuNCk

  • View mode Url of Presentation ex:
    1. https://docs.google.com/presentation/d/{long id}/pub?start=false&loop=false&delayms=60000 or
    2. https://docs.google.com/presentation/d/{long id}/embed
  • downloadable Url (permission was required):
    • https://docs.google.com/presentation/d/{long id}/export/pptx?id={long id}

Post to MovableType from Google Docs[edit]

MT 3.x[edit]

check Post via Google Docs


MT 2.x[edit]

  • API: MovableType API
  • URL: http://YOURBLOG/PATH/TO/mt-xmlrpc.cgi
    • If you met the HTTP 503 code(Service Unavailable), you can switch the URL from domain name to IP address.
  • Blog ID/Title
    • Blog ID is not work!
    • Blog title: It's Case-sensitive and was not support Chinese.

References[edit]

Retrieved from "https://wiki.planetoid.info/index.php?title=Google_Docs_and_Spreadsheets&oldid=25340"