# ♚尼腓工作室♚ (二) Google Apps Script(GAS) Google Excel 效能優化

## 前言
繼上篇文章[♚尼腓工作室♚ 利用Google Apps Script(GAS) 整理雲端硬碟資料到Excel](https://hackmd.io/@7o8lFAJGTgqk11Nd7vHtdw/B1TqrYGmU)，寫好的腳本已經能自動整理雲端硬碟的資料並輸出到Excel表單中，但隨著資料量的增加，有時就會出現逾時無法將指令跑完的問題。


因為GAS避免濫用對腳本有限制，而我遇到時間限制如下圖，詳細的限制條件可參考 : [這裡](https://developers.google.com/apps-script/guides/services/quotas)。


可以看得出來Google對於教育非常的重視，提供學生與企業相同的配額，但30分鐘還沒跑完....就代表需要優化程式了~

## 程式碼與流程圖
先來回顧一下我們的程式碼與流程圖。

function myFunction() {
var app = SpreadsheetApp.openById("1YhaO1Jb-KBJhIDOJnD_sKyWUhHdOhMyB1HzAjviYLFA");// 試算表金鑰
app.setName("動漫資料庫")
var sheet = app.getSheets()[0];//EXCEL第一表格(資料夾)[資料夾名、資料夾金鑰、圖片名、圖片網址]
var range = sheet.getDataRange();//取得ㄉ表格內內容
var values = range.getValues();//取得內容中的資料
sheet.setName("動漫資料夾")
var sheet2 = app.getSheets()[1];//EXCEL第二表格(影片總級數)[資料夾名、、影片金鑰、影片名、影片網址]
var range2 = sheet2.getDataRange();//取得表格內內容
var values2 = range2.getValues();//取得內容中的資料
sheet2.setName("影片資料庫")

var ImageFormat=["jpg","jpeg","png","tif","tiff","bmp"]//圖片格式
var Videoformat=["mp4","mkv","rmvb"]//影片格式

//存取表單現有內容

var FolderName=[];
for (var i = 0; i < values.length; i++) {
  FolderName[i] = values[i][0];
}
var VideoName=[];
for (var i = 0; i < values2.length; i++) {
  VideoName[i] = values2[i][2];
}
    var videoFolder = DriveApp.getFolderById("1YlkZ_iBFmUCbCTC_MQGb8DdpYE0bn0hd");//影片資料夾金鑰
    var foldersInVideoFolder = videoFolder.getFolders();//獲取目錄中所資料夾的集合
    var folder;
    var folderID =[];//影片資料夾金鑰

    for (var i = 0; foldersInVideoFolder.hasNext(); i++)  //hasNext()，確定調用是否next()將返回一個項目
    {
        folder = foldersInVideoFolder.next();//next()獲取文件或文件夾集合中的下一項。
        folderID[i] = folder.getId();//在 folderID[]中存影片資料夾金鑰
        //var data =[folder.getName(),folder.getId()]
        // sheet.appendRow(data);
    }
      for (var i = 0; i < folderID.length; i++){
      var folder = DriveApp.getFolderById(folderID[i]);//folder讀取folderID[]中存影片資料夾金鑰
      var files = folder.getFiles();//getFiles()獲取目錄中所有文件的集合
      var file; 
      var DataQuantity=0,run=0;
   for(var j=0;files.hasNext();j++){
      file=files.next();
      DataQuantity++;
      dotSplit = file.getName().split("."); //dotSplit取得文件名稱，並以"."來分割
      
    var count=0; count2=0//count圖片格式，count2影片格式
    
    while((count<ImageFormat.length)){
    
    if(dotSplit[dotSplit.length-1]==ImageFormat[count]){//判斷副檔名是否為圖片格式
    var iia =0;
    while(iia<FolderName.length){
    if(folder.getName()==FolderName[iia]){
    sheet.getRange(iia+1,1).setValue(folder.getName());
    sheet.getRange(iia+1,2).setValue(folder.getId());
    sheet.getRange(iia+1,3).setValue(file.getName());
    sheet.getRange(iia+1,4).setValue(file.getUrl());
    
    iia=FolderName.length;
    }else{//沒有相同名稱
    iia++
    if(iia==FolderName.length){
    var data =[folder.getName(),folder.getId(),file.getName(),file.getUrl()]
    sheet.appendRow(data);
    }
    }
    }
    //var data =[folder.getName(),folder.getId(),file.getName(),file.getUrl()]
    //sheet.appendRow(data);
    count=ImageFormat.length;//成功輸出過就暫停
    
    }else{count++,run++
    
    while(count2<Videoformat.length){
    
    if(dotSplit[dotSplit.length-1]==Videoformat[count2]){//判斷是否為影片
    var iic=0;
    while(iic<VideoName.length){
    if(file.getName()==VideoName[iic]){
    sheet2.getRange(iic+1,1).setValue(folder.getName());
    sheet2.getRange(iic+1,2).setValue(file.getId());
    sheet2.getRange(iic+1,3).setValue(file.getName());
    sheet2.getRange(iic+1,4).setValue("https://drive.google.com/file/d/"+file.getId()+"/preview");
    //sheet2.getRange(iic+1,4).setValue(file.getUrl());
    iic=VideoName.length;
    }else{
    iic++;
    if(iic==VideoName.length){
    var data =[folder.getName(),file.getId(),file.getName(),file.getUrl()]
    sheet2.appendRow(data);
    }
    }
    }
    
    //var data =[folder.getName(),file.getId(),file.getName(),file.getUrl()]
    //sheet2.appendRow(data);
    count2=Videoformat.length;
    }else{count2++;}
    }
    }   //繼續判斷副檔名
    }  
}

if(DataQuantity==run/ImageFormat.length){//如果資料夾中沒有圖片，判斷是否重複
  var iib=0;
  while(iib<FolderName.length){
  if(folder.getName()==FolderName[iib]){
//
//sheet.createTextFinder(folder.getName()).replaceAllWith(folder.getName(),folder.getId());//更新相同動但名稱資訊
sheet.getRange(iib+1,1).setValue(folder.getName());
sheet.getRange(iib+1,2).setValue(folder.getId());

  iib=FolderName.length;
  }else{//沒有相同名稱就新增
  iib++;
  if(iib==FolderName.length) {
  
  var dara=[folder.getName(),folder.getId()];
  sheet.appendRow(dara);
        }
  }    
    } 
      }    
        }
    //因為影片很亂所以來做排序
    sheet.sort(1);//影片資料夾名按照資料夾名稱排序
    sheet2.sort(3).sort(1);//動漫資料庫先以片名排序後再依照資料夾名稱排序
}

流程圖
## 第一步 找出問題所在
在Excel完全空白的情況下執行約8XX秒執行完成，而當Excel中已經有資料時執行腳本會比對資料來進行更新，花費的時間約9XX秒甚至會超過限制1800秒，進行資料比對時拖累整體的速度，可能的原因有以下幾點。

* ### 腳本迴圈過多
我編寫的GAS腳本有許多的迴圈，可能影響整體的運行速度，例

var ImageFormat=["jpg","jpeg","webp"]//圖片格式

while((count<ImageFormat.length)){
    
if(dotSplit[dotSplit.length-1]==ImageFormat[count]){//判斷副檔名是否為圖片格式

**說明**:```code 13```將副檔名放在陣列中，```code 51```行再依序使用迴圈來比對，而迴圈可能會增加運行時間，但理論上應該影響不大，~~如果這種迴圈會影響那他的電腦就太破了~~。

* ### 網路資源消耗
因為要判斷雲端硬碟中資料與Excel表單的資料是否重複，所以利用迴圈進行比對，這些指令會像雲端硬碟提出連線要求，如果這個方法又寫在迴圈中造成不必要的資源浪費，例

    while((count<ImageFormat.length)){
    
    if(dotSplit[dotSplit.length-1]==ImageFormat[count]){//判斷副檔名是否為圖片格式
    var iia =0;
    while(iia<FolderName.length){
    if(folder.getName()==FolderName[iia]){

![](https://i.imgur.com/sX96QaJ.png)

隨著檔案增加，連線次數會以指數性暴增造，很有可能就是拖慢腳本執行速度的罪魁禍首。


* ### 判定檔案順序不佳
在我的資料中有圖片與影片兩種檔案需要進行判定，判定的方法就是在自己設的迴圈中新增該檔案的副檔名，如下方的程式碼。

var ImageFormat=["jpg","jpeg","png","tif","tiff","bmp"]//圖片格式
var Videoformat=["mp4","mkv","rmvb"]//影片格式

經過先前的腳本運行可以知道圖片與影片的比例如下圖。

![](https://i.imgur.com/re0xySp.png)

由此可知影片的比例較多，但先前的腳本會先判定檔案是否為圖片，這樣就有94.3%(影片)多跑了圖片判定，造成速度的拖慢。





## 第三部 問題探討
### 迴圈過多解決辦法

if(dotSplit[dotSplit.length-1]="jpg"||
dotSplit[dotSplit.length-1]="jpeg"||
dotSplit[dotSplit.length-1]="webp"||){
輸出資料}

可以利用 ```if``` 的條件中 ```||``` 這個方式減少 ```While```、```for``` 等迴圈執行次數，但經過測試影響不大。
### 網路資源消耗 解決辦法

while((count<ImageFormat.length)){
if(dotSplit[dotSplit.length-1]==ImageFormat[count]){//判斷副檔名是否為圖片格式
var iia =0,var temp=folder.getName();
while(iia<FolderName.length){
if(temp==FolderName[iia]){

在迴圈外先宣告 ```var temp= folder.getName();``` 在迴圈內需要比較時利用 ```temp``` 比對就不會占用額外的網路資源，大大的減少運行時間。
### 判定檔案順序不佳 解決辦法
雖然就只需要將判定圖片與影片的程式碼調換順序，但由於先前的程式在編寫時是多個迴圈包再一起，直接調換順序容易出現錯，以及後續的維護不易，所以我打算以模組化的方式重新編寫，方便日後的更新與優化。
## 第四部 修正問題 
按照**第三部問題探討中**的優化方式我花了一些時間調整程式碼，又順帶發現了一個BUG，這邊檔案的判定順序是以堆疊(Stack)進行，愈晚上傳的影片會愈早讀取，然而有些資料夾在上傳資料時事先上傳圖片之後才上傳影片，所以就會造成這種狀況如下圖。

![](https://i.imgur.com/bTnG9bg.png)

這個原因就是第一個讀取的是影片，不是圖片，所以腳本判定該資料夾無圖片，但之後又發現有圖片造成兩次輸出，所以我多新增了 ```run``` 與 ```totocount``` 兩個變數， ```rum``` 在輸出圖片或影片時就會+1，而 ```totocount``` 會等於該資料夾的檔案數量，無圖片的判定必須要```run=totocount``` 時才會輸出，這樣就不會有圖片與無圖片條件都成立造成資料不一致的問題。


### 修正後執行成果
Excel完全空白執行腳本
![](https://i.imgur.com/SjSqOxH.png)
第二次執行時間
![](https://i.imgur.com/98GZ3vX.png)
### 修正後程式碼_Bata

function myFunction() {

var app = SpreadsheetApp.openById("1YhaO1Jb-KBJhIDOJnD_sKyWUhHdOhMyB1HzAjviYLFA");// 試算表金鑰
app.setName("動漫資料庫")
var sheet = app.getSheets()[0];//EXCEL第一表格(資料夾)[資料夾名、資料夾金鑰、圖片名、圖片網址]
var range = sheet.getDataRange();//取得ㄉ表格內內容
var values = range.getValues();//取得內容中的資料
sheet.setName("動漫資料夾")
var sheet2 = app.getSheets()[1];//EXCEL第二表格(影片總級數)[資料夾名、、影片金鑰、影片名、影片網址]
var range2 = sheet2.getDataRange();//取得表格內內容
var values2 = range2.getValues();//取得內容中的資料
sheet2.setName("影片資料庫")

var Imagetemp=["jpg","jpeg","png","webp","tif","tiff","bmp"]//圖片格式
var Videotemp=["mp4","mkv","rmvb","avi"]//影片格式

//存取表單現有內容

var FolderName=[];//取得Excel第一張表格資料夾名
for (var i = 0; i < values.length; i++) {
FolderName[i] = values[i][0];}
var VideoName=[];//取得Excel第二張表格影片檔名
for (var i = 0; i < values2.length; i++) {
VideoName[i] = values2[i][2];}
var videoFolder = DriveApp.getFolderById("1YlkZ_iBFmUCbCTC_MQGb8DdpYE0bn0hd");//影片資料夾金鑰
var foldersInVideoFolder = videoFolder.getFolders();//獲取目錄中所資料夾的集合
var folder;
var folderID =[];//影片資料夾金鑰

for (var i = 0; foldersInVideoFolder.hasNext(); i++){ //hasNext()，確定調用是否next()將返回一個項目
folder = foldersInVideoFolder.next();//next()獲取文件或文件夾集合中的下一項。
folderID[i] = folder.getId();//在 folderID[]中存影片資料夾金鑰
}
for (var i = 0; i < folderID.length; i++){
var folder = DriveApp.getFolderById(folderID[i]);//folder讀取folderID[]中存影片資料夾金鑰
var files = folder.getFiles();//getFiles()獲取目錄中所有文件的集合
var file;
for(var j=0;files.hasNext();j++){
file=files.next();
dotSplit = file.getName().split("."); //dotSplit取得文件名稱，並以"."來分割

var count=0,outputcount=0,range=0,temp=dotSplit[dotSplit.length-1],Name;
var run=0,totocount=folder.getFiles().length;
//判斷影片
while((outputcount==0)&&(count<Videotemp.length)){
if(temp==Videotemp[count]){//判斷副檔名是否為影片
Name=file.getName();
count=Videotemp.length;
outputcount=1;
}else{count++}}
//確認檔案為影片，開始判斷Excel中是否有重複
while((outputcount==1)&&(range<VideoName.length)){
if(Name==VideoName[range]){
sheet2.getRange(range+1,1).setValue(folder.getName());
sheet2.getRange(range+1,2).setValue(file.getId());
sheet2.getRange(range+1,3).setValue(file.getName());
sheet2.getRange(range+1,4).setValue("https://drive.google.com/file/d/"+file.getId()+"/preview");
outputcount++,run++;}else{range++;
if(range==VideoName.length){
var data =[folder.getName(),file.getId(),file.getName(),"https://drive.google.com/file/d/"+file.getId()+"/preview"];
sheet2.appendRow(data);
outputcount++,run++;} } } //影片判斷結束
//圖片判斷開始
count=0;
while((outputcount==0)&&(count<Imagetemp.length)){
if(temp==Imagetemp[count]){//判斷副檔名是否為圖片
Name=folder.getName();
count=Videotemp.length;
outputcount=1;
}else{count++}}
//確認檔案為圖片，開始判斷Excel中是否有重複
while((outputcount==1)&&(range<FolderName.length)){
if(Name==FolderName[range]){
sheet.getRange(range+1,1).setValue(folder.getName());
sheet.getRange(range+1,2).setValue(folder.getId());
sheet.getRange(range+1,3).setValue(file.getName());
sheet.getRange(range+1,4).setValue(file.getUrl());
outputcount++,run++;}else{range++;
if(range==FolderName.length){
var data =[folder.getName(),folder.getId(),file.getName(),file.getUrl()]
sheet.appendRow(data);
outputcount++,run++;} } } //有圖片判斷結束
//開始判斷無圖片
Name=folder.getName();
while((outputcount==0)&&(run==totocount)&&(range<FolderName.length)){
if(Name==FolderName[range]){
sheet.getRange(range+1,1).setValue(folder.getName());
sheet.getRange(range+1,2).setValue(folder.getId());
outputcount++,run++;}else{range++}}
if((outputcount==0)&&(range==FolderName.length)){
var data =[folder.getName(),folder.getId()];
sheet.appendRow(data);
outputcount++;}//無圖片判斷結束
} }
sheet.sort(1);//影片資料夾名按照資料夾名稱排序
sheet2.sort(3).sort(1);//動漫資料庫先以片名排序後再依照資料夾名稱排序
}

### 再次發現問題
我發現了Excel中有無資料會影響整體腳本的速度，原因是更新內容與新增內容的方法是不同的。

//新增的程式碼
var data =["文字一","文字二","文字三"];
sheet2.appendRow(data);
//更新的程式碼
sheet2.getRange(Excel行數,Excel列數).setValue("文字一");

我猜會影響速度是因為appendRow這個方法會向表單傳送指令要求回傳Excel中最下方無資料的表格，之後再進行輸出，這個作法可能會有網路延遲，而getRange這個方法必須要自己設定 (行數,列數) 這樣就不用像表單請求資料，所以速度比較快。

於是我在宣告兩個變數 ```FolderNamerange``` 與 ```VideoNamerange``` 讓分別等於Excel中圖片與影片的行數。

var FolderNamerange=FolderName.length;
var VideoNamerange=VideoName.length;

//舉例原本新增的方式，(速度較慢)
//var data =["文字一","文字二","文字三"];
//sheet2.appendRow(data);

//改成效能更佳的方式
sheet2.getRange(VideoNamerange+1,1).setValue("文字一");
sheet2.getRange(VideoNamerange+1,2).setValue("文字二");
sheet2.getRange(VideoNamerange+1,3).setValue("文字三");
//VideoNamerange+1是因為陣列會從0開始計算，+1才會在Excel中正常輸出
VideoNamerange++;
//輸出完VideoNamerange++讓下一筆需要新增資料可以跑到下一欄位，不會覆蓋到原本資料

### 優化後程式碼

優化後比較不同的是 ```appendRow("data");``` 會看到執行過程，如下圖，但整體速度較慢。

![](https://i.imgur.com/SgZpTwu.gif)

而 ```getRange(x,y).setValue("data");``` 要整體跑完後才會一次性顯示出來。

![](https://i.imgur.com/MnxAMYd.gif)

運行速度上無論是第一次跑或者後續更新都很穩定約92秒完成，程式碼優化後快了將近12倍，不再有超時問題。

![](https://i.imgur.com/W7Jg5Cp.png)

最終整理資料的腳本如下

function myFunction() {

var app = SpreadsheetApp.openById("1YhaO1Jb-KBJhIDOJnD_sKyWUhHdOhMyB1HzAjviYLFA");// 試算表金鑰
app.setName("動漫資料庫")
var sheet = app.getSheets()[0];//EXCEL第一表格(資料夾)[資料夾名、資料夾金鑰、圖片名、圖片網址]
var range = sheet.getDataRange();//取得ㄉ表格內內容
var values = range.getValues();//取得內容中的資料
sheet.setName("動漫資料夾")
var sheet2 = app.getSheets()[1];//EXCEL第二表格(影片總級數)[資料夾名、、影片金鑰、影片名、影片網址]
var range2 = sheet2.getDataRange();//取得表格內內容
var values2 = range2.getValues();//取得內容中的資料
sheet2.setName("影片資料庫")

var Imagetemp=["jpg","jpeg","png","webp","tif","tiff","bmp"]//圖片格式
var Videotemp=["mp4","mkv","rmvb","avi"]//影片格式

//存取表單現有內容
var FolderName=[];//取得Excel第一張表格資料夾名
for (var i = 0; i < values.length; i++) {
FolderName[i] = values[i][0];}
var VideoName=[];//取得Excel第二張表格影片檔名
for (var i = 0; i < values2.length; i++) {
VideoName[i] = values2[i][2];}
var FolderNamerange=FolderName.length,VideoNamerange=VideoName.length;
var videoFolder = DriveApp.getFolderById("1YlkZ_iBFmUCbCTC_MQGb8DdpYE0bn0hd");//影片資料夾金鑰
var foldersInVideoFolder = videoFolder.getFolders();//獲取目錄中所資料夾的集合
var folder;
var folderID =[];//影片資料夾金鑰

for (var i = 0; foldersInVideoFolder.hasNext(); i++){ //hasNext()，確定調用是否next()將返回一個項目
folder = foldersInVideoFolder.next();//next()獲取文件或文件夾集合中的下一項。
folderID[i] = folder.getId();//在 folderID[]中存影片資料夾金鑰
}
for (var i = 0; i < folderID.length; i++){
var folder = DriveApp.getFolderById(folderID[i]);//folder讀取folderID[]中存影片資料夾金鑰
var files = folder.getFiles();//getFiles()獲取目錄中所有文件的集合
var file;
for(var j=0;files.hasNext();j++){
file=files.next();
dotSplit = file.getName().split("."); //dotSplit取得文件名稱，並以"."來分割

var count=0,outputcount=0,range=0,temp=dotSplit[dotSplit.length-1],Name;
var run=0,totocount=folder.getFiles().length;

while((outputcount==0)&&(count<Videotemp.length)){//判斷影片
if(temp==Videotemp[count]){//判斷副檔名是否為影片
Name=file.getName();
count=Videotemp.length;
outputcount=1;
}else{count++}}
while((outputcount==1)&&(range<VideoName.length)){//確認檔案為影片，開始判斷Excel中是否有重複
if(Name==VideoName[range]){
sheet2.getRange(range+1,1).setValue(folder.getName());
sheet2.getRange(range+1,2).setValue(file.getId());
sheet2.getRange(range+1,3).setValue(file.getName());
sheet2.getRange(range+1,4).setValue("https://drive.google.com/file/d/"+file.getId()+"/preview");
outputcount++,run++;}else{range++;
if(range==VideoName.length){
sheet2.getRange(VideoNamerange+1,1).setValue(folder.getName());
sheet2.getRange(VideoNamerange+1,2).setValue(file.getId());
sheet2.getRange(VideoNamerange+1,3).setValue(file.getName());
sheet2.getRange(VideoNamerange+1,4).setValue("https://drive.google.com/file/d/"+file.getId()+"/preview");
outputcount++,run++,VideoNamerange++;} } } //影片判斷結束
count=0;//圖片判斷開始
while((outputcount==0)&&(count<Imagetemp.length)){
if(temp==Imagetemp[count]){//判斷副檔名是否為圖片
Name=folder.getName();
count=Videotemp.length;
outputcount=1;
}else{count++}}
while((outputcount==1)&&(range<FolderName.length)){//確認檔案為圖片，開始判斷Excel中是否有重複
if(Name==FolderName[range]){
sheet.getRange(range+1,1).setValue(folder.getName());
sheet.getRange(range+1,2).setValue(folder.getId());
sheet.getRange(range+1,3).setValue(file.getName());
sheet.getRange(range+1,4).setValue(file.getUrl());
outputcount++,run++;}else{range++;
if(range==FolderName.length){
sheet.getRange(FolderNamerange+1,1).setValue(folder.getName());
sheet.getRange(FolderNamerange+1,2).setValue(folder.getId());
sheet.getRange(FolderNamerange+1,3).setValue(file.getName());
sheet.getRange(FolderNamerange+1,4).setValue(file.getUrl());
outputcount++,run++,FolderNamerange++;} } } //有圖片判斷結束
//開始判斷無圖片
if(outputcount==0){
Name=folder.getName();
while((run==totocount)&&(range<FolderName.length)){
if(Name==FolderName[range]){
sheet.getRange(range+1,1).setValue(folder.getName());
sheet.getRange(range+1,2).setValue(folder.getId());
outputcount++,run++;}else{range++}}
if((outputcount==0)&&(range==FolderName.length)){
sheet.getRange(FolderNamerange+1,1).setValue(folder.getName());
sheet.getRange(FolderNamerange+1,2).setValue(folder.getId());
outputcount++,FolderNamerange++;}}//無圖片判斷結束
} }
sheet.sort(1);//影片資料夾名按照資料夾名稱排序
sheet2.sort(3).sort(1);//動漫資料庫先以片名排序後再依照資料夾名稱排序
}

## 結論
整理資料的腳本到這邊就告一段落了，理論上就算再塞入更多的檔案也不會造成超時的問題，寫程式真的需要謹慎的思考，看來我還要多練習，找Bug真的好累阿，下一步就是就是架設資料庫囉。

![](https://i.imgur.com/qzJABA6.png)