Mirochiu · June 6, 2022 09:27
diff --git a/datatable_with_google_sheet_api.html b/datatable_with_google_sheet_api.html
 <!DOCTYPE html>
 <html lang="zh-Hant-TW">

 <head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css" rel="stylesheet" />
 </head>

 <body>
    <h1>Datatable存取Google sheet API</h1>
    <hr />
    <table id="my-table" class="display">
        <thead>
            <tr>
                <th>表格欄位1</th>
                <th>表格欄位2</th>
                <th>表格欄位3</th>
            </tr>
        </thead>
    </table>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
    <script src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
    <script>
        $(document).ready(function () {
            console.log('ready'); // 除錯用

            var api_key = 'AIzaSyDdOWRZc03IltuK6PI_I-k_EVyiLBHiJ3c';
            var sheet_id = '14coU3RS4-CWK6QgfEu2hnY3UMRqLgmhYET9q3BI0_dY';
            var sheet_name = '工作表1';
            var range = 'A1:Q25';

            var api_url = 'https://sheets.googleapis.com/v4/spreadsheets/' +
                sheet_id + '/values/' +
                sheet_name + '!' + range +
                '?key=' + api_key;
            console.log('sheet_api_url', api_url); // 除錯用

            var datatableApi = $('#my-table').DataTable({
                ajax: {
                    type: 'GET',
                    url: api_url,
                    dataSrc: (json) => {
                        console.log('api response', json); // 除錯用
                        return json.values; // 資料來源直接就是物件
                    },
                    // 參考官方文件說明 https://datatables.net/manual/tech-notes/7
                    error: (e) => {
                        var emsg = '不明錯誤';
                        if (e) {
                            // 格式化json成人閱讀樣式
                            if (e.responseJSON) emsg = JSON.stringify(e.responseJSON, undefined, 2);
                            if (e.responseText) emsg = e.responseText;
                        }
                        console.error('發生錯誤,訊息為:' + emsg);
                    },
                    complete: () => console.log('存取結束'),
                },
            });
        });
    </script>

 </body>

 </html>
	<!DOCTYPE html>
	<html lang="zh-Hant-TW">

	<head>
	<meta charset="UTF-8">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<link href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css" rel="stylesheet" />
	</head>

	<body>
	<h1>Datatable存取Google sheet API</h1>
	<hr />
	<table id="my-table" class="display">
	<thead>
	<tr>
	<th>表格欄位1</th>
	<th>表格欄位2</th>
	<th>表格欄位3</th>
	</tr>
	</thead>
	</table>
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
	<script src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
	<script>
	$(document).ready(function () {
	console.log('ready'); // 除錯用

	var api_key = 'AIzaSyDdOWRZc03IltuK6PI_I-k_EVyiLBHiJ3c';
	var sheet_id = '14coU3RS4-CWK6QgfEu2hnY3UMRqLgmhYET9q3BI0_dY';
	var sheet_name = '工作表1';
	var range = 'A1:Q25';

	var api_url = 'https://sheets.googleapis.com/v4/spreadsheets/' +
	sheet_id + '/values/' +
	sheet_name + '!' + range +
	'?key=' + api_key;
	console.log('sheet_api_url', api_url); // 除錯用

	var datatableApi = $('#my-table').DataTable({
	ajax: {
	type: 'GET',
	url: api_url,
	dataSrc: (json) => {
	console.log('api response', json); // 除錯用
	return json.values; // 資料來源直接就是物件
	},
	// 參考官方文件說明 https://datatables.net/manual/tech-notes/7
	error: (e) => {
	var emsg = '不明錯誤';
	if (e) {
	// 格式化json成人閱讀樣式
	if (e.responseJSON) emsg = JSON.stringify(e.responseJSON, undefined, 2);
	if (e.responseText) emsg = e.responseText;
	}
	console.error('發生錯誤,訊息為:' + emsg);
	},
	complete: () => console.log('存取結束'),
	},
	});
	});
	</script>

	</body>

	</html>