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

前言

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

Image Not Showing Possible Reasons

The image file may be corrupted
The server hosting the image is unavailable
The image path is incorrect
The image format is not supported

Learn More →

因為GAS避免濫用對腳本有限制，而我遇到時間限制如下圖，詳細的限制條件可參考 : 這裡。

Image Not Showing Possible Reasons

The image file may be corrupted
The server hosting the image is unavailable
The image path is incorrect
The image format is not supported

Learn More →

可以看得出來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]){

code 56 的 folder.getName() 會像雲端硬碟連線請求檔案名稱，總共地連線次速約=總檔案數*(1+總檔案數)/2，圖表如下。

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

判定檔案順序不佳

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


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

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

由此可知影片的比例較多，但先前的腳本會先判定檔案是否為圖片，這樣就有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)進行，愈晚上傳的影片會愈早讀取，然而有些資料夾在上傳資料時事先上傳圖片之後才上傳影片，所以就會造成這種狀況如下圖。

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

修正後執行成果

Excel完全空白執行腳本

第二次執行時間

修正後程式碼_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"); 會看到執行過程，如下圖，但整體速度較慢。

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

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

最終整理資料的腳本如下



































































































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真的好累阿，下一步就是就是架設資料庫囉。

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

前言

程式碼與流程圖

第一步 找出問題所在

腳本迴圈過多

網路資源消耗

判定檔案順序不佳

第三部 問題探討

迴圈過多解決辦法

網路資源消耗 解決辦法

判定檔案順序不佳 解決辦法

第四部 修正問題

修正後執行成果

修正後程式碼_Bata

再次發現問題

優化後程式碼

結論

Read more

PHP 彈窗訊息轉址、跨網域存取Cookie

材質用料

♚尼腓工作室♚ 爬蟲 漢堡王GT 自動完成?

Untitled

第一步找出問題所在

第三部問題探討

網路資源消耗解決辦法

判定檔案順序不佳解決辦法

第四部修正問題

♚尼腓工作室♚ 爬蟲漢堡王GT 自動完成?