Gg / 透過Google Apps Script 建立上傳檔案頁面

Gg / 透過Google Apps Script 建立上傳檔案頁面

Post Views: 5,721

1.程式碼.gs

以Google Apps Script作為後端，執行跟google雲端硬碟與試算表之間的操作

#1~4 function doGet()

這個是讓網頁程式能夠在開啟時就能夠讀取formHtml

#8~10 function include(filename)

自定義函式，可以讓formHtml讀取 Google Apps Script的其他資料，例如：style.css、script.js

#13~120 function uploadFile(form)

接收前端傳過來的資料，在這裡判斷是否為有權限使用者、檔案類型、檔案大小等

如果有任何錯誤訊息，立即中止程式並傳回錯誤訊息給前端

其實也可以寫在前端網頁，但是這樣就很吃瀏覽器的資源

#124~162 function getInt(e)

透過前端網頁載入所有資料並建立DOM之後所觸發的function init()

將目前的google試算表資料回傳到前端的 function onStart()

作為開啟網頁時的初始資料

#166~171 function getScriptURL()

前端重新整理的按鈕觸發時，傳回網頁程式的網址

備註：當網頁程式被嵌入在其他網頁時，這個功能沒有作用，即使是回傳特定網址也是如此

#175~283 function moreFiles(obj)

跟function uploadFile(form)的差別在於function moreFiles(obj)是用來處理多個檔案

兩者接收的資料結構不一樣

function uploadFile(form)是直接接收前端單一檔案的表單資料

多個檔案的時候，在前端先分割檔案，再分別傳到 function moreFiles(obj)

應該也是可以直接傳到後端，只是就是在後端進行檔案分割

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
 
function doGet() {
  var output = HtmlService.createTemplateFromFile("formHtml").evaluate().setTitle("第一組研發資料上傳");
  output.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);//可以將發布成html介面的程式  崁入其他網站
  return output;
}
//
//
function include(filename) {
    return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
//
//一個檔案
function uploadFile(form) {
  //Logger.log(form.uploadFile.length);  
  //
  var userId = ["trico"];
  var antiMsg = "<span class='notie'>※※【請注意】※※<br></span>";
  var antiNum = 0;
  for(var u =0 ; u < userId.length ; u++){
      if(form.id !== userId[u]){ 
        //Logger.log(userId[u]);
        //Logger.log(userId.length);
        antiNum += 1;
        antiMsg = "<span class='notie'>無權限使用者!!</span></br>"; 
      }
  }
  if(antiNum > userId.length-1){
        return antiMsg;
      }
  //
  try {
      var sheetName = "第一組研發資料上傳列表";
      var foldername = "第一組研發資料";
      var folder, folders;

      var msg = "<span class='notie'>※※【請注意】※※<br></span>";

      folders = DriveApp.getFoldersByName(foldername);

      if (folders.hasNext()) {
          folder = folders.next();
      } else {
          folder = DriveApp.createFolder(foldername);
      }

      var fileExt = form.uploadFile.name;
      //取出副檔名
      fileExt = fileExt.substring(fileExt.lastIndexOf('.') + 1);
      
      var validExts = new Array("jpg", "doc", "docx", "pdf", "ppt", "pptx", "xls", "xlsx", "txt", "odt", "ods", "rar", "zip");
      
      if (form.id !== "" && form.uploadFile.length !== 0 && form.uploadFile.length < 10485760 && validExts.indexOf(fileExt) > -1) {
          var blob = form.uploadFile;
          var file = folder.createFile(blob);
          //var phone = "'" + form.phone.toString();
          file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
          file.setDescription("上傳者： " + form.id);
      } else {
          if (form.id === "")
              msg += "<span class='notie'>姓名遺漏</span></br>";
         // if (form.phone === "")
         //     msg += "<span class='notie'>電話遺漏</span></br>";
         // if (form.email === "")
         //     msg += "<span class='notie'>信箱遺漏</span></br>";
          if (form.uploadFile.length === 0)
              msg += "<span class='notie'>檔案遺漏</span></br>";
          if (form.uploadFile.length >= 10485760)
              msg += "<span class='notie'>檔案超過10MB</span></br>";
          if (validExts.indexOf(fileExt) < 0 && form.uploadFile.length !== 0)
              msg += "<span class='notie'>上傳的檔案格式為" + fileExt + ",只接受<strong  style=\"color:red\">" + validExts.toString() + "</strong></span></br>";
          return msg;
      }
      var fileUrl = file.getUrl();
      var fileName = file.getName();
      var FileIterator = DriveApp.getFilesByName(sheetName);
      var sheetApp = "";
      while (FileIterator.hasNext()) {
          var sheetFile = FileIterator.next();
          if (sheetFile.getName() == sheetName && sheetFile.getMimeType()=="application/vnd.google-apps.spreadsheet") {
              sheetApp = SpreadsheetApp.open(sheetFile);
          }
      }
      if (sheetApp == "") {
          sheetApp = SpreadsheetApp.create(sheetName);
          sheetApp.getSheets()[0].getRange(1, 1, 1, 4).setValues([["上傳時間", "姓名", "檔案名稱", "檔案網址"]]);
      }
      var sheet = sheetApp.getSheets()[0];
      var lastRow = sheet.getLastRow();

      //寫入資料表
      var targetRange = sheet.getRange(lastRow + 1, 1, 1, 4).setValues([[new Date().toLocaleString(), form.id, fileName, fileUrl]]);

      //寫出資料到網頁      
      var Sheet = sheetApp.getSheets()[0]
      var LastRow = Sheet.getLastRow();
      var LastColumn = Sheet.getLastColumn();
      var data = [];
      data.push("檔案上傳成功！");
      var listAll = Sheet.getSheetValues(1, 1, LastRow, LastColumn);
      var listA = [];
      for (var j = 0; j < listAll[0].length; j++) {
          listA.push(listAll[0][j]);
      }
      data.push(listA);
      for (var i = 1; i < listAll.length; i++) {
          var listOne = [];
          for (var k = 0; k < listAll[0].length; k++) {
            if(k==3){ //檔案網址 欄位
              listAll[i][k]= "<a href='" + listAll[i][k] +"' target='_blank'>" + listAll[i][k].trim() + "</a> ";
              listOne.push(listAll[i][k]);
            }else{
              listOne.push(listAll[i][k]);
            }
          }
          data.push(listOne);      }
      return JSON.parse(JSON.stringify(data));
  } catch (error) {
      return "檔案上傳失敗！ 原因：" + error.toString();
  }  
}
//
//網頁初始 抓取資料
//
function getInt(e) {
    var sheetName = "第一組研發資料上傳列表";
    var FileIterator = DriveApp.getFilesByName(sheetName);
    var sheetApp = "";
    while (FileIterator.hasNext()) {
        var sheetFile = FileIterator.next();
        if (sheetFile.getName() == sheetName) {
            sheetApp = SpreadsheetApp.open(sheetFile);
        }
    }    
    if (sheetApp == "") {
          sheetApp = SpreadsheetApp.create(sheetName);
          sheetApp.getSheets()[0].getRange(1, 1, 1, 4).setValues([["上傳時間", "姓名", "檔案名稱", "檔案網址"]]);
    }    
    var Sheet = sheetApp.getSheets()[0]
    var LastRow = Sheet.getLastRow();
    var LastColumn = Sheet.getLastColumn();
    var data = [];
    data.push("int");
    var listAll = Sheet.getSheetValues(1, 1, LastRow, LastColumn);
    var listA = [];
    for (var j = 0; j < listAll[0].length; j++) {
        listA.push(listAll[0][j]);
    }
    data.push(listA);
    for (var i = 1; i < listAll.length; i++) {
        var listOne = [];
        for (var k = 0; k < listAll[0].length; k++) {
          if(k==3){ //檔案網址
            listAll[i][k]= "<a href='" + listAll[i][k] +"' target='_blank'>" + listAll[i][k].trim() + "</a> ";
            listOne.push(listAll[i][k]);
          }else{
            listOne.push(listAll[i][k]);
          }            
        }
        data.push(listOne);
    }
    return JSON.parse(JSON.stringify(data));  
}
//
//重新整理網頁
//嵌入網頁 不能使用
function getScriptURL() {
  return ScriptApp.getService().getUrl();

  //前往特定網頁
  //return "https://sites.google.com/view/ntnupriori/%E7%AC%AC%E4%B8%80%E7%B5%84/%E6%9C%83%E8%AD%B0%E8%A8%98%E9%8C%84"; //https://sites.google.com/view/ntnupriori/第一組/會議記錄
}
//
// 超過一個檔案
//
function moreFiles(obj) {
  //Logger.log("超過一個檔案");  
  var userObj = obj.userName;
  //Logger.log(userObj);
  //Logger.log(obj.fileName);
  //Logger.log(obj.data.length);

  var userId = ["trico"];
  var antiMsg = "<span class='notie'>※※【請注意】※※<br></span>";
  var antiNum = 0;
  for(var u =0 ; u < userId.length ; u++){
      if(userObj !== userId[u]){ 
        //Logger.log(userId[u]);
        //Logger.log(userId.length);
        antiNum += 1;
        antiMsg = "<span class='notie'>無權限使用者!!</span></br>"; 
      }
  }
  if(antiNum > userId.length-1){
        return antiMsg;
  }
  //
  try {
      var sheetName = "第一組研發資料上傳列表";
      var foldername = "第一組研發資料";
      var folder, folders;

      var msg = "<span class='notie'>※※【請注意】※※<br></span>";

      folders = DriveApp.getFoldersByName(foldername);
      if (folders.hasNext()) {
          folder = folders.next();
      } else {
          folder = DriveApp.createFolder(foldername);
      }

      var fileExt = obj.fileName;
      fileExt = fileExt.substring(fileExt.lastIndexOf('.') + 1);
      
      var validExts = new Array("jpg", "doc", "docx", "pdf", "ppt", "pptx", "xls", "xlsx", "txt", "odt", "ods", "rar", "zip");
      
      if (userObj !== "" &&  obj.data.length < 10485760 && validExts.indexOf(fileExt) > -1) {
          var blob = Utilities.newBlob(Utilities.base64Decode(obj.data), obj.mimeType, obj.fileName);
          var file = folder.createFile(blob);
          //var phone = "'" + form.phone.toString();
          file.setDescription("上傳者： " + userObj.id);
      } else {
          if (userObj === "")
              msg += "<span class='notie'>姓名遺漏</span></br>";
         // if (form.phone === "")
         //     msg += "<span class='notie'>電話遺漏</span></br>";
         // if (form.email === "")
         //     msg += "<span class='notie'>信箱遺漏</span></br>";
          if (obj.data.length === 0)
              msg += "<span class='notie'>檔案遺漏</span></br>";
          if (obj.data.length >= 10485760)
              msg += "<span class='notie'>檔案超過10MB</span></br>";
          if (validExts.indexOf(fileExt) < 0 && obj.data.length !== 0)
              msg += "<span class='notie'>上傳的檔案「"+ obj.fileName +"」格式為" + fileExt + ",只接受<strong  style=\"color:red\">" + validExts.toString() + "</strong></span></br>";
          return msg;
      }
      var fileUrl = file.getUrl();
      var fileName = file.getName();
      var FileIterator = DriveApp.getFilesByName(sheetName);
      var sheetApp = "";
      while (FileIterator.hasNext()) {
          var sheetFile = FileIterator.next();
          if (sheetFile.getName() == sheetName) {
              sheetApp = SpreadsheetApp.open(sheetFile);
          }
      }
      if (sheetApp == "") {
          sheetApp = SpreadsheetApp.create(sheetName);
          sheetApp.getSheets()[0].getRange(1, 1, 1, 4).setValues([["上傳時間", "姓名", "檔案名稱", "檔案網址"]]);
      }
      var sheet = sheetApp.getSheets()[0];
      var lastRow = sheet.getLastRow();
      
      var targetRange = sheet.getRange(lastRow + 1, 1, 1, 4).setValues([[new Date().toLocaleString(), userObj, fileName, fileUrl]]);
      
      var Sheet = sheetApp.getSheets()[0]
      var LastRow = Sheet.getLastRow();
      var LastColumn = Sheet.getLastColumn();
      var data = [];
      data.push("檔案上傳成功！");
      var listAll = Sheet.getSheetValues(1, 1, LastRow, LastColumn);
      var listA = [];
      for (var j = 0; j < listAll[0].length; j++) {
          listA.push(listAll[0][j]);
      }
      data.push(listA);
      for (var i = 1; i < listAll.length; i++) {
          var listOne = [];
          for (var k = 0; k < listAll[0].length; k++) {
            if(k==3){ //檔案網址
              listAll[i][k]= "<a href='" + listAll[i][k] +"' target='_blank'>" + listAll[i][k].trim() + "</a> ";
              listOne.push(listAll[i][k]);
            }else{
              listOne.push(listAll[i][k]);
            }
          }
          data.push(listOne);
      }
      return JSON.parse(JSON.stringify(data));
  } catch (error) {
      return "檔案上傳失敗！ 原因：" + error.toString();
  }
  //return DriveApp.createFile(blob).getId();
}
Pages: 1 2 3 4
Tags: