LoginSignup
10
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@matsuhandy(handy matsu)

Google Apps Script(GAS)でスプレッドシートを扱うクラス(J3-02)

Last updated at Posted at 2017-02-09

0. 前回の復習課題

＜課題3＞
　GoogleDrive上に今日の日付のフォルダを作り，logというファイル名のドキュメントを作成，ファイルIDを取得せよ。次のGASプログラムを動作させ，結果が図のようになることを確認せよ。mainを読んでどのように文字の属性を指定しているか理解せよ。属性を設定してから解除するまで，書き込む文字すべてに指定した属性が適用されるようになっている。

文字の属性（装飾）を指定してから文字を書く
var logID = "**********";//各自のIDに置き換え

function main(){
  var log = new Doc(logID);
  log.clear();
  log.print("解答例です\n");
  
//  log.setUnderline(true);
//  log.print("アンダーライン\n");
//  log.setUnderline(false);
  
  log.setBold(true);
  log.print("ボールド\n");
  log.setBold(false);
  
  log.setItalic(true);
  log.print("italic・イタリック\n");//全角文字ではイタリックにならないようだ
  log.setItalic(false);
  
  var bkup = log.getFontSize();
  log.setFontSize(20);
  log.print("フォントサイズ20\n");
  log.setFontSize(bkup);

//  var bkup = log.getForegroundColor();
//  log.setForegroundColor("#ff0000");//red
//  log.print("赤色\n");
//  log.setForegroundColor(bkup);
  
  var bkup = log.getBackgroundColor();
  log.setBackgroundColor("#ffff00");//yellow
  log.print("背景が黄色\n");
  log.setBackgroundColor(bkup);
  
}

//////////Docクラスの定義開始（コンストラクタとメンバ関数で構成）
//Docクラスのコンストラクタの記述
Doc = function(id){
  this.ID = id;
  this.doc = DocumentApp.openById(this.ID); 
  this.body = this.doc.getBody();
  this.docText = this.body.editAsText();
  this.st = this.body.getText().length;//元の文字数，= 0を修正220428
  this.ed = this.st;//属性を適用する最後の文字end，= 0を修正220428
  this.bold = false;
  this.underline = false;
  this.italic = false;
  this.fontSize = this.docText.getFontSize();//デフォルトのフォントサイズをセット
  this.foregroundColor = this.docText.getForegroundColor();//デフォルトの文字色をセット
  this.backgroundColor = this.docText.getBackgroundColor();//デフォルトの背景色をゲット
}
//Docクラスのメンバ関数の定義開始
//メソッドprintの定義，テキスト追加
Doc.prototype.print = function(str){
  //this.st = this.body.getText().length;//コメントアウト220428
  this.docText.appendText(str);//文字列の追加
  this.ed += str.length;//追加後の文字数//this.ed = this.body.getText().length;でもよい

  this.docText.setBold(this.st,this.ed-1,this.bold);
  this.docText.setUnderline(this.st,this.ed-1,this.underline);
  this.docText.setItalic(this.st,this.ed-1,this.italic);
  this.docText.setFontSize(this.st,this.ed-1, this.fontSize);
  this.docText.setForegroundColor(this.st,this.ed-1, this.foregroundColor);
  this.docText.setBackgroundColor(this.st,this.ed-1, this.backgroundColor);
  
  this.st = this.ed;
}
//メソッドreplaceの定義，文字列置き換え
Doc.prototype.replace = function(src,dst){
  this.body.replaceText(src,dst);
}
//メソッドclearの定義，全消去
Doc.prototype.clear = function(){
  this.st = this.ed = 0;//追加220428
  this.body.clear();
}
//メソッドgetIDの定義，ファイルIDを返す
Doc.prototype.getID = function(){
  return this.ID;
}
//メソッドsetBoldの定義
Doc.prototype.setBold = function(bold){
  this.bold = bold;
}
//メソッドsetForegroundColorの定義
Doc.prototype.setForegroundColor = function(color){
//ここを作成
}
//メソッドgetForegroundColorの定義
Doc.prototype.getForegroundColor = function(){
//ここを作成
}
//メソッドsetBackgroundColorの定義
Doc.prototype.setBackgroundColor = function(color){
  this.backgroundColor = color;
}
//メソッドgetBackgroundColorの定義
Doc.prototype.getBackgroundColor = function(){
  return this.backgroundColor;
}
//メソッドsetFontSizeの定義
Doc.prototype.setFontSize = function(size){
  this.fontSize = size;
}
//メソッドgetFontSizeの定義
Doc.prototype.getFontSize = function(){
  return this.fontSize;//現在のフォントサイズをゲット
}
//メソッドsetUnderlineの定義
Doc.prototype.setUnderline = function(underline){
//ここを作成
}
//メソッドsetItalicの定義
Doc.prototype.setItalic = function(italic){
  this.italic = italic;
}
//////////Docクラスの定義終了
  • 実行結果

170424-1.PNG

＜課題3の続き＞
　main関数のリマーク分になっているプログラムが動作するようにDocクラスのメソッドを追加せよ。ここを参考にする。結果は次の図のようになる。前回，ボールドやフォントサイズ等を変更するメソッドを作成したが，ドキュメントでそれらを適用する場所を何文字目から何文字目までと設定するのがとても面倒だった。今回はそれが改良されている。メソッドprintの中身を読み，どのようなアルゴリズムでそれができているかよく理解すること。完成したlogをワードファイル「log.docx」としてダウンロードし，提出せよ。完成したソースも，テキストエディタにコピーペーストして「kadai3.txt」として提出せよ。
　なお、GoogleDrive上でlogを開いてもアンダーラインが見えないというバグがある（250417現在）。カーソルを持っていくとアンダーラインがあることにはなっている。ワードファイルとしてダウンロードして開くと見えるので，ダウンロードして確認せよ。

  • 実行結果（この結果になるように，メソッドを追加して，mainのリマークを削除する）

170424-2.PNG

1. GASでスプレッドシートを扱うクラス

  • スプレッドシートのファイル名を取得できる。
  • シートを増やしたり名前を付けられる。
  • 任意のシートの任意のセルに値を入れる
  • 任意のシートの任意のセルの値を読み出せる。
  • 任意のシートの任意のセルに色を付ける。
  • 次で解説されている，クラスDocをコピー・ペーストして使用している。（もちろん＜課題3＞で作成したDocクラスを使っても構わない。）
    http://qiita.com/matsuhandy/items/fb88cc52f8bfe5ce8dbc
  • クラスを使うことで同時に複数のスプレッドシートを楽に扱える。
複数のスプレッドシートを扱う例
logID = "**********";//手動でDocumentを新規作成しIDを調べておく
ssID1 = "++++++++++";//手動でSpreadsheetを新規作成しIDを調べておく
ssID2 = "----------";//手動でSpreadsheetを新規作成しIDを調べておく

function main(){
  log = new Doc(logID);
  ss1 = new Ssheet(ssID1);
  ss2 = new Ssheet(ssID2);

  log.printTodayNow();
  log.print("プログラムスタート"+'\n');

  log.print(ss1.getFileName()+'\n');//Spreadsheetのファイル名を表示
  log.print(ss2.getFileName()+'\n');//Spreadsheetのファイル名を表示
  ss1.setValue(0,1,1,'スプレッドシート1');
  ss2.setValue(0,1,1,'スプレッドシート2');
  //ss2.clear(0,1,1);
  
  ss1.setValue(0,1,2,123);//0シートのB1セルの値をセット
  ss1.insertSheet(2);//Spreadsheetの枚数を2枚にする
  ss1.renameSheet(1,"test");//2枚目の名前を変更
  ss1.setBackgroundColor(0,1,1,255,0,0)//0シートの左上のセルを赤色に変更  
  ss1.setValue(1,1,1,"2枚目のシート")//1シートの左上のセルに値を代入  

  log.printTodayNow();
  log.print("プログラム終了"+'\n');
}

//////////Ssheetクラスの定義開始（コンストラクタとメンバ関数で構成）
//Ssheetクラスのコンストラクタの記述
Ssheet = function(id){
  this.ssFile = SpreadsheetApp.openById(id);
  this.ssFileName = this.ssFile.getName();
  SpreadsheetApp.setActiveSpreadsheet(this.ssFile);//値を返さない, fopenと同様
  this.activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
}
//Ssheetクラスのメンバ関数（メソッド）の定義開始
//spreadsheetのファイル名を返すメソッド
Ssheet.prototype.getFileName = function(){
  return this.ssFileName;
}
//spreadsheetのsheetでrow行cal列にデータを入れるメソッド
Ssheet.prototype.setValue = function(sheet,row,col,value){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var cell = this.activeSheet.getRange(row,col);
  cell.setValue(value);
}
//spreadsheetのsheetでrow行cal列をクリアするメソッド
Ssheet.prototype.clear = function(sheet,row,col,value){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var cell = this.activeSheet.getRange(row,col);
  cell.clear(value);
}
//spreadsheetのsheetからrow行のcal列のデータをもらってくるメソッド
Ssheet.prototype.getValue = function(sheet,row,col) {
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var value = this.activeSheet.getRange(row, col).getValue();
  return value;
}
//背景の色を設定するメソッド
Ssheet.prototype.setBackgroundColor = function(sheet,row,col, r,g,b) {
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var cell = this.activeSheet.getRange(row,col);
  cell.setBackgroundRGB(r,g,b);
}
//spreadsheetのsheet数を指定の数増やすメソッド
Ssheet.prototype.insertSheet = function(num){
  var sheetNum = this.activeSpreadsheet.getNumSheets();
  while(num>sheetNum){
    this.activeSpreadsheet.insertSheet();
    sheetNum++;
  }
}
//spreadsheetのsheetの名前をセットするメソッド
Ssheet.prototype.renameSheet = function(sheet,newName){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  this.activeSheet.setName(newName);
}
//////////Ssheetクラスの定義終了




//////////Docクラスの定義開始（コンストラクタとメンバ関数で構成）
//Docクラスのコンストラクタの記述
Doc = function(id){
  this.ID = id;
  this.doc = DocumentApp.openById(this.ID); 
  this.body = this.doc.getBody();
  this.docText = this.body.editAsText();
}
//Docクラスのメンバ関数の定義開始
//メソッドprintの定義，テキスト追加
Doc.prototype.print = function(str){
  this.docText.appendText(str);
}
//メソッドreplaceの定義，文字列置き換え
Doc.prototype.replace = function(src,dst){
  this.body.replaceText(src,dst);
}
//メソッドclearの定義，全消去
Doc.prototype.clear = function(){
  this.body.clear();
}
//メソッドgetIDの定義，ファイルIDを返す
Doc.prototype.getID = function(){
  return this.ID;
}
//指定秒数のウェイト，表示動作を遅らせたい時などに使用
Doc.prototype.waitSec = function(sec){
  var start = new Date().getSeconds();
  while((new Date().getSeconds()-start) < sec);
}
//指定ミリ秒のウェイト，表示動作を遅らせたい時などに使用
Doc.prototype.waitMiliSec = function(msec){
  var start = new Date(); //new Date()は，「1970年1月1日午前0時」からの通算ミリ秒を返す
  while((new Date()-start) < msec);
}
//今現在の日時を表示
Doc.prototype.printTodayNow = function(){
  var now = new Date();
  var year = now.getYear();
  var month = now.getMonth() + 1;
  var day = now.getDate();
  var hour = now.getHours();
  var min = now.getMinutes();
  var sec = now.getSeconds();
  this.docText.appendText(year +'_'+ ("0"+month).slice(-2) +'_'+ ("0"+day).slice(-2) +' '+ 
    ("0"+hour).slice(-2) +'-'+ ("0"+min).slice(-2) +'-'+ ("0"+sec).slice(-2));
}
//////////Docクラスの定義終了
  • **********や++++++++++等は，手動で新規作成してURLからIDを調べて置き換える。
  • IDについては，次に解説あり。

  • スプレッドシートを扱うクラスだけをまとめておく。
スプレッドシートを扱うクラスSsheet
//////////Ssheetクラスの定義開始（コンストラクタとメンバ関数で構成）
//Ssheetクラスのコンストラクタの記述
Ssheet = function(id){
  this.ssFile = SpreadsheetApp.openById(id);
  this.ssFileName = this.ssFile.getName();
  SpreadsheetApp.setActiveSpreadsheet(this.ssFile);//値を返さない
  this.activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
}
//Ssheetクラスのメンバ関数（メソッド）の定義開始
//spreadsheetのファイル名を返すメソッド
Ssheet.prototype.getFileName = function(){
  return this.ssFileName;
}
//spreadsheetのsheetでrow行cal列にデータを入れるメソッド
Ssheet.prototype.setValue = function(sheet,row,col,value){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var cell = this.activeSheet.getRange(row,col);
  cell.setValue(value);
}
//spreadsheetのsheetでrow行cal列をクリアするメソッド
Ssheet.prototype.clear = function(sheet,row,col,value){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var cell = this.activeSheet.getRange(row,col);
  cell.clear(value);
}
//spreadsheetのsheetからrow行のcal列のデータをもらってくるメソッド
Ssheet.prototype.getValue = function(sheet,row,col) {
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var value = this.activeSheet.getRange(row, col).getValue();
  return value;
}
//背景の色を設定するメソッド
Ssheet.prototype.setBackgroundColor = function(sheet,row,col, r,g,b) {
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  var cell = this.activeSheet.getRange(row,col);
  cell.setBackgroundRGB(r,g,b);
}
//spreadsheetのsheet数を指定の数増やすメソッド
Ssheet.prototype.insertSheet = function(num){
  var sheetNum = this.activeSpreadsheet.getNumSheets();
  while(num>sheetNum){
    this.activeSpreadsheet.insertSheet();
    sheetNum++;
  }
}
//spreadsheetのsheetの名前をセットするメソッド
Ssheet.prototype.renameSheet = function(sheet,newName){
  this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
  this.activeSheet.setName(newName);
}
//////////Ssheetクラスの定義終了

＜課題4＞
　上のサンプルプログラムを実行し，複数シートへの書き込みや１枚目のシートのデータを読んで，２枚目に書き込むなどができることを確認して，次の課題を解け。

  • ここから，zipファイルをダウンロード（下図）して，デスクトップ等に解凍せよ。

170424-3-1.png

  • 解凍したエクセルファイルをGoogleDriveの今日の日付のフォルダにアップロードせよ。
  • 右クリック，アプリで開く，スプレッドシート で開いて，ファイルメニューから，「Google スプレッドシートとして保存」を選択すると，GoogleSpreadsheetに変換され，新しく開かれる。ファイル名は拡張子が取れた形になっている。

変換.png

できたファイル.png

  • Google Spreadsheetのkadai4を再度開け。
  • 「内訳（手動入力）」シートに，架空で構わないので，物品名，数量，単価等を5件以上，手動入力せよ。金額については，通常のエクセルと同様に，数式で自動計算させる。
  • GASのプログラムで，金額の合計を求め，領収書シートの，金額，に入力せよ。（エクセルと同様に数式処理した方が早いが，今回はGASで書け）
  • その他，今日の日付，所属（出席番号(3Jxx)），氏名をGASプログラムで，入力して完成させよ。手動は不可。日付はDateクラスを利用せよ。
  • 完成したkadai4をエクセルとしてダウンロードし，提出せよ。またGASプログラムもテキストエディタにコピーペーストして，「kadai4.txt」として提出せよ。
10
14
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
10
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?