gas(google app script)でデザインや数式の入ったシートから値だけのシートにしてダウンロード

Google App Scriptを書いてみた

初めてのAppScriptだけど、すごく便利だったので、メモ

スプレッドシートから値だけを取り出してCSV的にする

スプレッド・シートは結構活用するのですが、スプレッドシートに数式や参照が含まれていて そのままコピペして送るのがちょっとあれだなと思ったので、スプレッドシートをコピーして数式を外した

Excelならコピーと貼り付けオプションでできるし、Gsuiteでももちろんできるんだけど

シートの枚数が多いのでめんどくさくなってきた。なのでGAS書いてみた。

gas であれこれやるサンプルになる

GASであれこれやるのに、参考資料として使えそうなので、資料を残すことに。

• シートの作成
• シートの選択
• シートのコピー
• ファイル形式変換して取得
• メール送信

この辺のサンプルになるなと。

gas.js

function MailSpreadSheet(){
  
  // created_at : 2018-12-25 
  // auther     : takuya_1st 
  // description: 指定したファイルから数式とデザインを除外して、値だけのファイルにして、メールにして送る
  
  var file_id = "13OAD2yER60___GoogleDriveのファイルID____UMNw5MrSVA";
  var mail_to = "takuya@example.com";
  var subject = "実験-"+ (new Date()).toISOString();
  var body = "これは実験です。";
  var attachment_files =  [];
  
  var fetchOpt = {
    "headers" : { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
    "muteHttpExceptions" : true
  };
  
  /// 新規スプレッドシートを作る。
  const createNewSpreadSheetFile = function (){
    // https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app
    const name = (new Date()).toISOString()
    const app = SpreadsheetApp.create(name);
    // Logger.log(app.getUrl());
    return app
  }

  const CopyFileToNewFile = function ( src_file_id ){
    // 元ファイルを取得
    const src_doc = SpreadsheetApp.openById(src_file_id)

    // 新規ファイルを作成:シートのコピー先ファイルを追加。
    const dest = createNewSpreadSheetFile();  
    const dest_file = SpreadsheetApp.openByUrl(dest.getUrl());

    // すべてのシートについて
    //    作業用一時シートに値で貼り付けて
    //    新規ファイルへシートをコピー
    for ( idx in src_doc.getSheets() ){
      const sheet = src_doc.getSheets()[idx];
      const range = src_doc.getDataRange();
      
      // 新規シートを作って選択範囲をコピペ。
      const temp_sheet_name = (new Date()).toISOString();
      const temp_sheet = src_doc.insertSheet(temp_sheet_name);
      const temp_range = temp_sheet.getDataRange();
      range.copyTo(temp_range,{contentsOnly:true});
      // 新規ファイルへシートをコピー
      const dest_sheet =temp_sheet.copyTo(dest_file);
      dest_sheet.setName( sheet.getName() );//名前を元シートに合わせる。
      //一時的なシートを削除
      src_doc.deleteSheet(temp_sheet);
    }
    
    // 新規ファイルからファイル作成時の空白シート（先頭）を削除
    dest_file.deleteSheet( dest_file.getSheets()[0] );
    
    //コピーされたファイルを返す。
    return dest_file;
  }
  const fetchFileAsExcel = function( file_id ){
    const file = DriveApp.getFileById(file_id);
    const fileName = file.getName();
    xlsxName = fileName + "-変換-" + (new Date()).toLocaleString() + ".xlsx";
    //const blob = file.getAs( MimeType.MICROSOFT_EXCEL )
    //blob.setName(xlsxName);
    //return blob
    // もし blob が取れないときはURL経由
    fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + file.getId() + "&exportFormat=xlsx&format=xlsx";
    const blob = UrlFetchApp.fetch(fetchUrl, fetchOpt).getBlob().setName(xlsxName);
    return blob
  }
  
  const SendMail = function(){    
    
    MailApp.sendEmail(mail_to, subject, body, {attachments:attachment_files});
  }
 
  
  
  const main = function() {
    const file = CopyFileToNewFile(file_id);
    const blob = fetchFileAsExcel( file.getId() );
    
    DriveApp.removeFile(DriveApp.getFileById(file.getId()));
    attachment_files.push(blob);
    body = body + blob.getName();
    
    SendMail();
  }
  
  
  main();
}