Getting Data from Google Spreadsheet or Excel

# Getting Data from Google Spreadsheet or Excel 작성중. Excel 같은 형태로 data를 관리하면 편할때가 많음. 뭐 프로그래밍에 익숙한 분들이라면 SQL 같은걸 이용할수도 있겠지만, 간단한 데이터의 경우 이런것보다 간단하게 excel이나 google spreadsheet 같은 것을 이용하는 것이 훨씬 간편하고 좋음. 그런데 이 data를 인터넷에서 잘 보여주고 싶을때는 excel이 아닌 html / javascript array / JSON (JavaScript Object Notation) 형태로 편집을 해줘야 할텐데... 엑셀이나 google spreadsheet의 데이터를 html로 편집 가능한 형태로 변환해 봅시다. ## PH

• 2024-02-09 : To SEE.

## TOC ## Copy and Paste by plain text, into html / JavaScript array / JSON 가장 무식한 방법같긴 한데, 직관적이고 쉽고 Google API가 바뀌어도 문제없고 등 좋은점도 많을듯? 아;;;; 그런데 왜 empty cell은 복사할때 tab같은게 빠져서 복사되냐. 엑셀(Excel)로 복붙할땐 또 제대로 붙여지는게 이상하기도... 당장은 엑셀로 붙여넣고 다시 plain text로 붙여넣거나 empty cell이 없도록 만들거나 해서 해결하면 될거 같은데, 여간 짱나네. 이건 당연히 자동으로는 update 안된다는 치명적인 단점도. ```[.scrollable.lang-js] // String to Array m.encloseStr = function (str) { if (!str || str.constructor !== String) { return ''; } if (str.charAt(0) === '"' || /[\n\t]/.test(str)) { return `"${str.replace(/"/g, '""')}"`; } return str; }; m.strToJSON = function (str, colMap = true, rowMap = false) { if (!str || str.constructor !== String) { return Promise.resolve(str); } if (str.charAt(str.length - 1) !== "\n") { str += "\n"; } const ret = []; const delimiter = /([^\t\n]*)([\t\n])/g; const lastQuote = /[^"](?:"")*"([\t\n])/g; let exec; let start = 0; let row = -1, col = -1, delim = "\n"; let strElem = ""; function increaseRC(delim) { if (delim === '\t') { col++; return true; } else if (delim === '\n') { row++; col = 0; ret.push([]); return true; } return false; } while (start < str.length && increaseRC(delim)) { if ((str.substring(start, start + 1)) === '"') { lastQuote.lastIndex = start + 1; if ((exec = lastQuote.exec(str)) !== null) { strElem = str.substring(start + 1, lastQuote.lastIndex - 2); delim = exec[1]; start = delimiter.lastIndex = lastQuote.lastIndex; } else { strElem = str.substring(start + 1); delim = ""; start = str.length; } strElem = strElem.replace(/""/g, '"'); } else { if ((exec = delimiter.exec(str)) !== null) { strElem = exec[1]; delim = exec[2]; start = delimiter.lastIndex; } else { strElem = str.substring(start); delim = ""; start = str.length; } } ret[row][col] = strElem; } if (colMap) { const firstColSize = ret[0].length; for (let i = 0; i < ret.length; i++) { let jMax = ret[i].length > firstColSize ? firstColSize : ret[i].length; for (let j = 0; j < firstColSize; j++) { let key = ret[0][j]; if (j < jMax) { ret[i][key] = ret[i][j]; } else { ret[i][key] = ""; } } } } if (rowMap) { for (let i = 0; i < ret.length; i++) { let key = ret[i][0]; ret[key] = ret[i]; } } return Promise.resolve(ret); }; m.csvToJSON = function (str, colMap = true, rowMap = false) { if (!str || str.constructor !== String) { return Promise.resolve(str); } let rows = str.split("\n"); for (let i = 0; i < rows.length; i++) { if (rows[i].substring(0, 1) === '"' && rows[i].substring(rows[i].length - 1) === '"') { rows[i] = rows[i].substring(1, rows[i].length - 1).split('","'); } else { rows[i] = rows[i].split(","); } } if (colMap) { const firstColSize = rows[0].length; for (let i = 0; i < rows.length; i++) { let jMax = rows[i].length > firstColSize ? firstColSize : rows[i].length; for (let j = 0; j < jMax; j++) { let key = rows[0][j]; if (key !== undefined) { rows[i][key] = rows[i][j]; } } } } if (rowMap) { for (let i = 0; i < rows.length; i++) { let key = rows[i][0]; if (key !== undefined) { rows[key] = rows[i]; } } } return Promise.resolve(rows); }; m.arrayToTableHTML = function (txtArray) { let tableStr = "<table>"; for (let row = 0; row < txtArray.length; row++) { tableStr += "<tr>"; for (let col = 0; col < txtArray[row].length; col++) { tableStr += `<td>${m.escapeOnlyTag(txtArray[row][col]).replace(/\n/g, '<br>')}</td>`; } tableStr += "</tr>"; } tableStr += "</table>"; return tableStr; }; ```/

Google Spreadsheet Example: kipid's google docu - Spreadsheet Data Example

```[.scrollable.lang-html] <div class="p"></div> Multi line text (error when single line text starts with '"'.) <data id="GSD-Ex0" class="none">"Multi-line text can be a cell data (string) of google spreadsheet or MS excel, using ""Alt+Enter""." "Then these multiline texts are enclosed by double quotation mark '""'." "To distinguish the actual double quotation mark "" from the one enclosing multiline text, the actual double quotation mark is replaced by '""""'. (It is doubled.)" """ the first quotation and the last quotation "" should be treated in plain text in proper way." "Single line text which starts with '"' "test</data> <div class="table-wrapper" id="table-GSD-Ex0"></div> <div class="p"></div> Empty cells (error): copied directly from Google Spreadsheet. <data id="GSD-Ex1" class="none">"There can be empty cells." B1 is empty. A2 and B2 are empty too. A3 is empty. "In excel, empty cells are properly copied to plain text." "But in google spreadsheet, empty cells are simply ingnored. In other words, no multiple tabs are there in plain text copied from google spreadsheet." Column 1 Column 2 Column 3</data> <div class="table-wrapper" id="table-GSD-Ex1"></div> <div class="p"></div> Empty cells: copied from MS office Excel which is copied from Google Spreadsheet. <data id="GSD-Ex2" class="none">"처음에 두줄 넘기면" 가나다 d d 뮻ㅇ ㄷㄹ호ㅑㅓㅏㅣ; "두 줄 이상 쓰는거지 alt+enter 이렇게?" a 계속 있고 "이걸로" 하면? "따옴표로 둘러 싸기는 하는데, 내용 안에 ""따옴표""가 들어가면 어떻게 처리되지?" abcd 처음에 따옴표를 써보자. """처음에 따옴표를 쓰고" d 처음에 따옴표를 써보자. "처음에 따옴표 쓰고 그냥 끝나면? 마지막에" d as 따옴표를 쓰면?""" 그렇게?" d d "따옴표"" 다음에 바로 엔터가 오면?" 따옴표" 다음에 탭이 오면? s a 9960 d ㅁ ㅠ ㅊ ㅂ</data> <div class="table-wrapper" id="table-GSD-Ex2"></div> ```/

Multi line text (error when single line text starts with '"'.) "Multi-line text can be a cell data (string) of google spreadsheet or MS excel, using ""Alt+Enter""." "Then these multiline texts are enclosed by double quotation mark '""'." "To distinguish the actual double quotation mark "" from the one enclosing multiline text, the actual double quotation mark is replaced by '""""'. (It is doubled.)" """ the first quotation and the last quotation "" should be treated in plain text in proper way." "Single line text which starts with '"' "test

Empty cells (error): copied directly from Google Spreadsheet. "There can be empty cells." B1 is empty. A2 and B2 are empty too. A3 is empty. "In excel, empty cells are properly copied to plain text." "But in google spreadsheet, empty cells are simply ingnored. In other words, no multiple tabs are there in plain text copied from google spreadsheet." Column 1 Column 2 Column 3

Empty cells: copied from MS office Excel which is copied from Google Spreadsheet. "처음에 두줄 넘기면" 가나다 d d 뮻ㅇ ㄷㄹ호ㅑㅓㅏㅣ; "두 줄 이상 쓰는거지 alt+enter 이렇게?" a 계속 있고 "이걸로" 하면? "따옴표로 둘러 싸기는 하는데, 내용 안에 ""따옴표""가 들어가면 어떻게 처리되지?" abcd 처음에 따옴표를 써보자. """처음에 따옴표를 쓰고" d 처음에 따옴표를 써보자. "처음에 따옴표 쓰고 그냥 끝나면? 마지막에" d as 따옴표를 쓰면?""" 그렇게?" d d "따옴표"" 다음에 바로 엔터가 오면?" 따옴표" 다음에 탭이 오면? s a 9960 d ㅁ ㅠ ㅊ ㅂ

## Code sample using JavaScript 복잡스럽다. Google spreadsheet script sample.

## RRA

Google developers

1. Google Developers - Google Spreadsheets API version 3.0
2. Google Developers - Google Apps Script - Extending Google Docs; and Custom Functions in Spreadsheets
3. Google Developers - Reading Data from a Spreadsheet into JavaScript Objects

Related Posts

4. RAVELRUMBA's blog - Getting JSON out of Google Spreadsheets, using PHP (server-side script); and misoproject.com - Datasets from Google Spreadsheets, using JavaScript (client-side script)
5. Dataist - Using Google Spreadsheet as a database
6. stackoverflow.com - Fetch JSON from Google Spreadsheet using Javascript
7. stackoverflow.com - Getting value of a cell from google docs spreadsheet into javascript