[Google Sheet Script] 使用sidebar+html导入Excel文件
Index.html
1 <!DOCTYPE html> 2 <html> 3 4 <head> 5 <base target="_top"> 6 <link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.2.0/css/bootstrap.min.css" rel="stylesheet" /> 7 <link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-fileinput/5.5.0/css/fileinput.min.css" rel="stylesheet" /> 8 <link href="https://cdnjs.cloudflare.com/ajax/libs/limonte-sweetalert2/11.4.24/sweetalert2.min.css" rel="stylesheet" /> 9 </head> 10 11 <body> 12 <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script> 13 <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script> 14 <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script> 15 <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.2.0/js/bootstrap.min.js"></script> 16 <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-fileinput/5.5.0/js/fileinput.min.js"></script> 17 <script src="https://cdnjs.cloudflare.com/ajax/libs/limonte-sweetalert2/11.4.24/sweetalert2.min.js"></script> 18 19 <div class="container"> 20 21 <form class="mt-3"> 22 <div class="form-group mb-3"> 23 <label class="form-label">Choose File</label> 24 <input id="importFile" type="file" class="file form-control" data-show-preview="false" data-show-upload="false"> 25 </div> 26 <div class="form-group mb-3"> 27 <label class="form-label">File Title Info Preview</label> 28 <textarea id="Title_Json" class="form-control" readonly disabled></textarea> 29 </div> 30 <div class="form-group mb-3"> 31 <label class="form-label">File Table Info Preview</label> 32 <textarea id="Table_Json" class="form-control" readonly disabled></textarea> 33 </div> 34 <div class="form-group text-center mb-3"> 35 <button id="btnSubmit" class="btn btn-primary" onclick="ImportData()" disabled>Submit</button> 36 </div> 37 </form> 38 39 <div class="alert alert-primary" role="alert"> 40 Tip! Preview is for check if load Excel success . Please make sure Preview has data to show before submit . 41 </div> 42 </div> 43 44 <script> 45 46 $(document).ready(function () { 47 48 $("#importFile").fileinput({ 49 maxFileCount: 1 50 }); 51 52 $('#importFile').on('fileclear', function(event) { 53 $('#Title_Json').val(''); 54 $('#Table_Json').val(''); 55 $("#btnSubmit").attr("disabled",true); 56 }); 57 58 }); 59 60 document.getElementById('importFile').addEventListener('change', handleFileSelect, false); 61 62 var ExcelToJSON = function () { 63 this.parseExcel = function (file) { 64 var reader = new FileReader(); 65 reader.onload = function (e) { 66 var data = e.target.result; 67 var workbook = XLSX.read(data, { type: 'binary' }); 68 69 //Read spefic cell value 70 var titleInfo = []; 71 titleInfo.push([workbook.Sheets[workbook.SheetNames[0]]['B1'] == undefined ? "" : workbook.Sheets[workbook.SheetNames[0]]['B1'].v]); 72 titleInfo.push([workbook.Sheets[workbook.SheetNames[0]]['B2'] == undefined ? "" : workbook.Sheets[workbook.SheetNames[0]]['B2'].v]); 73 titleInfo.push([workbook.Sheets[workbook.SheetNames[0]]['B3'] == undefined ? "" : workbook.Sheets[workbook.SheetNames[0]]['B3'].v]); 74 titleInfo.push([workbook.Sheets[workbook.SheetNames[0]]['B4'] == undefined ? "" : workbook.Sheets[workbook.SheetNames[0]]['B4'].v]); 75 titleInfo.push([workbook.Sheets[workbook.SheetNames[0]]['B5'] == undefined ? "" : workbook.Sheets[workbook.SheetNames[0]]['B5'].v]); 76 titleInfo.push([workbook.Sheets[workbook.SheetNames[0]]['B6'] == undefined ? "" : workbook.Sheets[workbook.SheetNames[0]]['B6'].v]); 77 titleInfo.push([workbook.Sheets[workbook.SheetNames[0]]['B7'] == undefined ? "" : workbook.Sheets[workbook.SheetNames[0]]['B7'].v]); 78 79 jQuery('#Title_Json').val(JSON.stringify(titleInfo)); 80 81 var range = XLSX.utils.decode_range(workbook.Sheets[workbook.SheetNames[0]]['!ref']); 82 range.s.c = 0; //start column 83 range.e.c = 16; //end column 84 range.s.r = 7; //start row 85 var newRange = XLSX.utils.encode_range(range); 86 //defval --> default value for empty cell , blankrows --> skip empty row or not , true : not skip , false : skip 87 jQuery('#Table_Json').val(JSON.stringify(XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]], { header: 1, defval: "", range: newRange , blankrows: true }))); 88 89 $("#btnSubmit").attr("disabled",false); 90 }; 91 reader.onerror = function (ex) { 92 //showError(ex); 93 google.script.run.alert(ex); 94 }; 95 reader.readAsBinaryString(file); 96 }; 97 }; 98 99 function handleFileSelect(evt) { 100 var files = evt.target.files; // FileList object 101 var xl2json = new ExcelToJSON(); 102 xl2json.parseExcel(files[0]); 103 } 104 105 function ImportData() { 106 var fileInfo = {}; 107 fileInfo.titleInfo = $('#Title_Json').val(); 108 fileInfo.tableInfo = $('#Table_Json').val(); 109 110 google.script.run.loadData(fileInfo); 111 112 $(".fileinput-remove-button").click(); 113 } 114 115 function showError(error){ 116 Swal.fire({ 117 icon: 'error', 118 title: 'Oops...', 119 text: 'Something went wrong!' + error , 120 }); 121 } 122 123 </script> 124 125 </body> 126 127 </html>
Code.gs
1 /** 2 * A special function that runs when the spreadsheet is first 3 * opened or reloaded. onOpen() is used to add custom menu 4 * items to the spreadsheet. 5 */ 6 function onOpen() { 7 var ui = SpreadsheetApp.getUi(); 8 ui.createMenu('Google Script') 9 .addItem('Upload File', 'showSidebar') 10 .addToUi(); 11 } 12 13 /** 14 * Show sidebar with spefic page 15 */ 16 function showSidebar() { 17 var html = HtmlService.createHtmlOutputFromFile('Index').setTitle('Upload File'); 18 SpreadsheetApp.getUi().showSidebar(html); 19 } 20 21 /** 22 * Called in web script via google.script.run 23 */ 24 function loadData(fileInfo){ 25 try{ 26 27 let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1'); 28 29 var values = JSON.parse(fileInfo.titleInfo); 30 sheet.getRange(1, 2, values.length, values[0].length).setValues(values); 31 32 values = JSON.parse(fileInfo.tableInfo); 33 values.shift(); // The first element of the array is the header information，if not import , should be shift. 34 sheet.getRange(9, 1, values.length, values[0].length).setValues(values); 35 } 36 catch(error){ 37 Logger.log(error); 38 SpreadsheetApp.getUi().alert("Upload File get some error ! " + error); 39 } 40 }
效果图：
