Google Apps Script > Excel > 自動複製特定 Sheet 並格式化更改 Sheet 名稱

※ 注意！本章節是針對個人用途使用。並且是第一次練習使用 Google Apps Script，內有有諸多潦草不成熟的寫法，僅供參考。若有更好的方式歡迎留言交流，感謝。

自動複製指定 Sheet 並根據其他 Sheet 自動更名

Google Apps Script 裡面使用的 Code

主要啟動區程式碼

• 我自己寫個 go() 當作主要串連所有動作的 function，_for_a、_for_b...等等只是為了針對不同檔案做一樣的連續動作。
• 所以設定時間觸發的時候是直接觸發 function _for_a 或 function _for_b。就會自動執行 _for_a function 的連動指令。

• 詳細設定按時自動觸發，請參考 Google Apps Script/觸發設定。
  基本上目前已經可以由介面直接設定。

//整組連貫動作
function go(excel_id){
  //var copy_sheet_is = 0;
  
  //從 備用（無人更新時請自行取用） 複製 Sheet（來源與目標相同 Excel）
  copy_sheet_new(excel_id,excel_id,"備用（無人更新時請自行取用）");
  
  //從舊有 Sheet 透過 Replace Regex 分析萃取出 年月日，並推算日期進位，下回規則設定產生新日期後，產生新的 Sheet 名稱
  //之後把上面複製出來的 Copy of 備用（無人更新時請自行取用） 改成新名稱
  
  //但這塊也是最容易出 Bug 的地方
  
  sheet_copy_name(excel_id,"Copy of 備用（無人更新時請自行取用）");

  
  //Debug 用(非必要)：只有在會多產生出異物才做刪除
  //刪除程序多產生的 Copy of 備用（無人更新時請自行取用）
  //  del_sheet(excel_id,"Copy of 備用（無人更新時請自行取用）");
  

  //觀察最後結果用
   var ss = open_excel(excel_id);
   var sheet = ss.getSheets()[0];
   Logger.log("最後第一個[0]Sheet是:"+sheet.getSheetName());   //sheet.showSheet();
}

//for A檔 Excel
function _for_a(){
  go("0Avb1e3AASsaLdDM2UkliWTBhM29uaGg1ZlpRdFQmc");
}

//for B檔 Excel
function _for_b(){
  go("0Avb1e3AASsaLdGpEeGkxcVhvS1pMYjkzNQY3U1ZWc");
};

基本 Excel 控制

//讀取 Excel ok
function open_excel(excel_id){
  //Source set
  //參考://https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app
  
  var ss = SpreadsheetApp.openById(excel_id);
  //Logger.log(ss.getName());
  return ss;
}



//Sheet 重新命名 ok
//https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#copy(String)
function sheet_rename(excel_id,sheet_target_name,sheet_new_name){
  
  var ss = open_excel(excel_id);
  
  var target_sheet = ss.getSheetByName(sheet_target_name);
  var target_new_name_test = ss.getSheetByName(sheet_new_name); //防止發生新命名與原有項目重複
  if((target_sheet!=null)&&(target_new_name_test==null)){
    Logger.log(target_sheet.getSheetName());
    target_sheet.setName(sheet_new_name);
  }
}



//刪除指定 Sheet (ByName) ok
function del_sheet(excel_id,sheet_name){
  var ss = open_excel(excel_id);
  var sheet = ss.getSheetByName(sheet_name);
  if(sheet!=null){
    ss.deleteSheet(sheet);
  }
}


//Sheet 顯示 (ByName) ok
function show_sheet(excel_id,sheet_target_name){

  var ss = open_excel(excel_id);
  
  //var sheet = ss.getSheets()[0];
  var sheet = ss.getSheetByName(sheet_target_name);
  Logger.log(sheet.getSheetName());

  sheet.showSheet();
}

//Sheet 隱藏 (ByName) ok
function hide_sheet(excel_id,sheet_target_name){
  var ss = open_excel(excel_id);
  
  //var sheet = ss.getSheets()[0];
  var sheet = ss.getSheetByName(sheet_target_name);
  Logger.log(sheet.getSheetName());
  
  sheet.hideSheet();
}

重點 Excel 控制項目 加強控制

//根據指定 Sheet 複製 Sheet 到指定檔案（有防止複製已經複製過的項目）
function copy_sheet_new(source_excel_id,target_excel_id,sheet_name){
 
  var ss = open_excel(source_excel_id);
  
  //set target sheet by .getSheet 參考:https://developers.google.com/apps-script/reference/spreadsheet/sheet#copyTo(Spreadsheet)
  
  //var sheet = ss.getSheets()[0];
  var target_sheet = ss.getSheetByName(sheet_name);
  var target_sheet_have = ss.getSheetByName("Copy of " + sheet_name);
  
  if((target_sheet!=null)&&(target_sheet_have==null)){
    Logger.log(target_sheet.getSheetName());
    
    var destination = open_excel(target_excel_id); //目標 Excel 檔案
    target_sheet.copyTo(destination); //將來源檔案中指定的 Sheet 複製到目標 Excel 檔案裡面（會出現在最前面）

 //debug: 莫名其妙會複製了次的時候，讓他自動刪掉多複製的
 
    
    var sheet_1st_now = ss.getSheets()[0];
        var sheet_1st_now_name = sheet_1st_now.getSheetName();
    if (sheet_1st_now_name == "Copy of " + sheet_name + " 1"){
      //sheet_1st_now.clear();
      del_sheet(target_excel_id,"Copy of " + sheet_name + " 1");
    }



/*
    if (sheet_1st_now_name == "Copy of 備用（無人更新時請自行取用） 1"){
      //sheet_1st_now.clear();
      del_sheet(target_excel_id,"Copy of 備用（無人更新時請自行取用） 1");
    }
*/
  }else{
    Logger.log("沒有找到指定 Sheet 或是複製過了");
  }
}

客製重點核心：根據所需規則

• 主要目的於核心動作全都寫在這
• 本次目的：
  1. 複製範本 Sheet 然後更改其 Sheet 名稱。
  2. 其 Sheet 名稱必須參照舊有的特定格式，並做日期規則性地增進位修改。
     • 遞增規則：以星期為主，二四六才有活動。
       • 程式中就要歸納出如何讓六跳到下週二，結論是遇到六才讓日=+3，二四=+2，其餘不處理。
       • 小重點：日數＞月份天數，就要把日數=-月份天數；月份超過（大於等於）12，就要月數=-12、年++
• 其中還有小狀況要處理：確實捕捉到對的 Sheet 參考名稱格式，這部分由「get_last_title」處理。
  • 原理是透過排除範本 Sheet 的名稱特徵，其餘的照順位就能取得日期最近的舊有參考 Sheet 名稱格式。

//主要動作內容：根據非範例的已存在紀錄Sheet的名稱，取得格式並推導下次日期。之後為新複製的更名為下次日期。
function sheet_copy_name(excel_id,sheet_target_name){
  var ss = open_excel(excel_id);  
    
  //get_last_title(excel_id,主要排除的sheet name)
  var last_title = get_last_title(excel_id,sheet_target_name);
  Logger.log("取得的分析來源標題為："+last_title);

  //用來解決 抓錯名格式參考 Sheet：排除範例與其複製的可能性的第一順位Sheet應該就是最新的參考日期
  
  //var sheet = ss.getSheets()[2];//var sheet = ss.getSheets()[2];
  
  var sheet = ss.getSheetByName(last_title);
  if(sheet!=null){
    Logger.log("上次日期：：：：：：：：：：：：：：：：：：：：：：：：：：：" + sheet.getSheetName());
        
    //取出上次紀錄標題中的日間紀錄
    var last_play_day = sheet.getSheetName();
  
//假設推移觀察(預先測是可能的意外狀況)：
//last_play_day = "";
    
    //利用 Regex Replace 刪除法把不要的部分選取起來 "" 取代
    var last_day = last_play_day.replace(/.{0,20}/,"");
    var last_day = parseInt(last_day.replace(/.{0,7}$/,""));
    
    var last_month = last_play_day.replace(/.{0,17}/,"");
    var last_month = parseInt(last_month.replace(/.{0,10}$/,""));

    var last_year = last_play_day.replace(/.{0,12}/,"");
    var last_year = parseInt(last_year.replace(/.{0,13}$/,""));

    var last_week_name = last_play_day.replace(/.{0,23}/,"");
    var last_week_name = last_week_name.replace(/.{0,5}$/,"");
    
    //目前開放規則，只開週二、四、六(另外順便日數在週六的時候改成+3)
    switch(last_week_name) {
      case "二":
        var new_week_name = "四";
        var next_day = last_day + 2;
        break;
      case "四":
        var new_week_name = "六";
        var next_day = last_day + 2;
        break;
      case "六":
        var new_week_name = "二";
        var next_day = last_day + 3;
        break;
      default:
        //
    }
    
    //為了日期進位所以設定各月總天數
    switch(last_month) {
      case 1:
        var last_month_day = 31;
        break;
      case 2:
        var last_month_day = 28;
        break;
      case 3:
        var last_month_day = 31;
        break;
      case 4:
        var last_month_day = 30;
        break;
      case 5:
        var last_month_day = 31;
        break;
      case 6:
        var last_month_day = 30;
        break;
      case 7:
        var last_month_day = 31;
        break;
      case 8:
        var last_month_day = 31;
        break;
      case 9:
        var last_month_day = 30;
        break;
      case 10:
        var last_month_day = 31;
        break;
      case 11:
        var last_month_day = 30;
        break;
      case 12:
        var last_month_day = 31;
        break;
      default:
        //
    }
    
    //處理日期進位
    if(next_day>last_month_day){
      next_day = next_day - last_month_day;//next_day = next_day - 30;
      last_month = last_month + 1;
      if(last_month>12){
        last_month = last_month -12;
        last_year = last_year + 1;
      }
    }
    
    //補零(同時變回字串)
    if(next_day < 10){
      next_day = "0" + next_day;
    }
    //補零(同時變回字串)
    if(last_month < 10){
      last_month = "0" + last_month;
    }
    
    var next_time = last_play_day.replace(/\d............/,last_year + "." + last_month + "." + next_day + "(" + new_week_name + ")");

    Logger.log("！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！" + next_time);
    sheet_rename(excel_id,sheet_target_name,next_time);
    
    //隱藏舊有 Sheet 
    hide_sheet(excel_id,last_title);
  }
}


//觀察 sheet_copy_name 並 Debug：目前是要排除取用到的舊資料不是紀錄資料。也就是要排除 Copy of 備用（無人更新時請自行取用）以及 備用（無人更新時請自行取用
//目前為 sheet_copy_name 裡面提供來源參考標題 last_title
function get_last_title(excel_id,sheet_target_name){
  var ss = open_excel(excel_id);  
 
  
  var sheet_test = ss.getSheets()[0];
  var sheet_test_name = sheet_test.getSheetName();
  //sheet_test_name = sheet_test_name.replace("Copy of ","");//這一定會有BUG，例如早就存在0與1位置上分別為 A 跟 copy A ，經過這一道不管是哪個當目標進來都會被當 A，不過因為下面觀察的時候又會手動加回 Copy 所以這個 BUG 又抵銷了，最終目的還是有達到同判斷出A 與 copy A 最後要排除這兩項。
  sheet_test_name = sheet_test_name.replace(/.*）/,""); // 利用 （無人更新時請自行取用） 的全形下括號特徵 透過 Regex 把有含有這個項目的全都特徵統一話並抓出來，跳過他
  
  var sheet_test1 = ss.getSheets()[1];
  var sheet_test1_name = sheet_test1.getSheetName();
  sheet_test1_name = sheet_test1_name.replace(/.*）/,"");
  
  var sheet_test2 = ss.getSheets()[2];
  var sheet_test2_name = sheet_test2.getSheetName();
  sheet_test2_name = sheet_test2_name.replace(/.*）/,"");
  
  var sheet_test3 = ss.getSheets()[3];
  var sheet_test3_name = sheet_test3.getSheetName();
  sheet_test3_name = sheet_test3_name.replace(/.*）/,"");
  
  //var last_day = last_play_day.replace("Copy of ","");
  
  //sheet_test.getSheetName()!="Copy of " + sheet_target_name 沒有意義。因為進來的本來就要做 "Copy of 備用（無人更新時請自行取用）"
  //所以要增加排除的其實是 Regex Copy of 之後的
/*  
  if((sheet_test.getSheetName()!=sheet_target_name)&&(sheet_test.getSheetName()!="Copy of " + sheet_test_name)){
    Logger.log("[0]:" + sheet_test.getSheetName());
  }

  if((sheet_test1.getSheetName()!=sheet_target_name)&&(sheet_test1.getSheetName()!="Copy of " + sheet_test1_name)){
    Logger.log("[1]:" + sheet_test1.getSheetName());
  }
  
  if((sheet_test2.getSheetName()!=sheet_target_name)&&(sheet_test2.getSheetName()!="Copy of " + sheet_test2_name)){
    Logger.log("[2]:" + sheet_test2.getSheetName());
  }
*/

  if (sheet_test_name==""){
    Logger.log("排除0");
    if (sheet_test1_name==""){
      Logger.log("排除1");
      if (sheet_test2_name==""){
        Logger.log("排除2");
        if (sheet_test3_name==""){
          Logger.log("排除3");
        }else{
          Logger.log("3:" + sheet_test3.getSheetName());
          return sheet_test3.getSheetName();
        }  
      }else{
        Logger.log("2:" + sheet_test2.getSheetName());
        return sheet_test2.getSheetName();
      }  
    }else{
      Logger.log("1:" + sheet_test1.getSheetName());
      return sheet_test1.getSheetName();
    }    
  }else{
    Logger.log("0:" + sheet_test.getSheetName());
    return sheet_test.getSheetName();
  }
 
  //這支 Function 的目標是：retrun 一個排除過的參考名稱 String 回去給重點核心的 var sheet 當成 ByName 的目標（也是可以By序號但回傳就要回傳數字代號）
}

其他舊版不採用的 function

//沒有目標的舊版本
//還沒寫成 ByName 的 Sheet copy

//改良版在重點項目 function copy_sheet_new

function copy_sheet(source_excel_id,target_excel_id){
  
  //Source set  參考://https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app
  
  var ss = SpreadsheetApp.openById(source_excel_id);
  Logger.log(ss.getName());
  
  //set target sheet by .getSheet 參考:https://developers.google.com/apps-script/reference/spreadsheet/sheet#copyTo(Spreadsheet)
  var sheet = ss.getSheets()[0];
  
  var destination = SpreadsheetApp.openById(target_excel_id); //目標 Excel 檔案
  sheet.copyTo(destination); //將來源檔案中指定的 Sheet 複製到目標 Excel 檔案裡面（會出現在最前面）
  
}

其他測試期間亂用的 function

//基礎測試
function test(){
  //copy_sheet("0AiiCzROFD-FrdEJQblRJVkN6RmpKeWU5ZUpWbnFyUlE","0AiiCzROFD-FrdEJQblRJVkN6RmpKeWU5ZUpWbnFyUlE");
  //hide_sheet("0AiiCzROFD-FrdEJQblRJVkN6RmpKeWU5ZUpWbnFyUlE");
  //show_sheet("0AiiCzROFD-FrdEJQblRJVkN6RmpKeWU5ZUpWbnFyUlE");
  //sheet_rename("0AiiCzROFD-FrdEJQblRJVkN6RmpKeWU5ZUpWbnFyUlE","A 的副本","0");
}



//get_last_title(excel_id,主要排除的sheet name)
//  var last_title = get_last_title("0AiiCzROFD-FrdGhmSy1LbmpLQXJQSk9kUW8zZmRRN0E","Copy of 備用（無人更新時請自行取用）");
//  Logger.log("取得的分析來源標題為："+last_title);

//測試重複、連續產生四次 go()
/*
function gogo(){
  go();go();go();go();
};
*/

以下是 @Wiki 平台的廣告