GASで各スプレッドシートよりセルの情報を集める

Question

前提・実現したいこと

####　前提
わたしはプログラミング初心者で、毎日ググりながら、問題を解決を考える程度のスペックの持ち主です。
本当に初心者なので、全くいいプログラミングの書き方ではないのは百も承知ですが、だからこそ皆さんのお力をお借りできればと思います。

####　現状
現在社内の業務効率化に向けてスプレッドシートの改良を行っています。
社員が毎月各スプレッドシートで勤務時間を記入するシートがあり、各々から数値を確認し、確からしいかチェックした後に、一括管理するスプレッドシートにコピペをします。

そのコピペの作業が面倒なので、一括管理するスプレッドシートからボタン一つで各スプレッドシートの特定のセルにある情報を吸い上げて、一覧にそのまま数値を反映させたいと思います。

数値を吸い上げるまではかなったのですが、それらを一括管理するスプレッドシートに反映させるのに困っています。

発生している問題・エラーメッセージ

エラーは出ていませんが、吸い上げたデータを配列に格納したいのですが、どうすればいいのかわかりません。

該当のソースコード

以下で、各社員のスプレッドシートが保存されているファイルを先月の月末の数字で名付けてあり、各社員のファイルは、作業月報_社員の苗字_月末の数字という名付け方です。

 function paidHolidaySet(){
 
  //社員を配列データとして格納
    var users = [ 'akito','fujisawa','inoue']; 
    var file_names = users.map(function(element) {

  //月末lmld=lastmonthlastday
  var dt = new Date();
  var lmld = new Date(dt.getFullYear(), dt.getMonth(), 0)
  
  // YYYYMMDD形式に変換
  var year_str = lmld.getFullYear();
  var month_str = lmld.getMonth()+ 1; // monthはなんと0-11
  var day_str = lmld.getDate();

  var month_str = ('0' + month_str).slice(-2);
  var day_str = ('0' + day_str).slice(-2);

  var format_str = 'YYYYMMDD';
  format_str = format_str.replace(/YYYY/g, year_str);
  format_str = format_str.replace(/MM/g, month_str);
  format_str = format_str.replace(/DD/g, day_str);
      
     　　return "作業月報_" + element + "_" + format_str; 
 　　});
    
   Logger.log(file_names);
    
        //フォルダーの中にある各ファイルにアクセスし、IDを取得
        var fileIDs = file_names.map(function(element){
           //ファイル名をユーザー名・月末で指定する
            //月末lmld=lastmonthlastday
           var dt = new Date();
           var lmld = new Date(dt.getFullYear(), dt.getMonth(), 0)
  
            // YYYYMMDD形式に変換
           var year_str = lmld.getFullYear();
           var month_str = lmld.getMonth()+ 1; // monthはなんと0-11
           var day_str = lmld.getDate();

           var month_str = ('0' + month_str).slice(-2);
           var day_str = ('0' + day_str).slice(-2);

           var format_str = 'YYYYMMDD';
           format_str = format_str.replace(/YYYY/g, year_str);
           format_str = format_str.replace(/MM/g, month_str);
           format_str = format_str.replace(/DD/g, day_str);
       //フォルダーの名前を指定
           var folderName = format_str ; 
           folderName = folderName.toString();
      
    //フォルダー名をドライブから検索し、IDを取得
           var folderID = DriveApp.getFoldersByName(folderName).next().getId();
 // Logger.log(folderID);
            return DriveApp.getFolderById(folderID).getFilesByName(element).next().getId();
    });
　Logger.log(fileIDs);
    
        
    //スプレッドシートアプリから取得したIDのスプレッドシートを起動し、アクティブシート取得
    var sheet = fileIDs.map(function(element){
     var sht=  SpreadsheetApp.openById(element);
        var st = sht.getActiveSheet();
         var array = [];
         var latestPaidHour = st.getRange( 1,1 ).getValues();
         latestPaidHour = latestPaidHour.toString();
      for ( var i = 0; i < latestPaidHour.length; i++ ){
      　　return array.push(latestPaidHour);
      }
      Logger.log(latestPaidHour);
    });
 Logger.log(sheet);

}
  

試したこと

最初は配列一つで、その中で作業が完結されるような書き方をしたかったのですが、何度やっても失敗したので、配列の数値を、

1．ファイル名の指定
2．ファイルIDの指定
3．各ファイルにアクセスして、指定のセルから数値を取得し、配列に格納

という順序で更新していくような形にしてみました。
最後の段階で、指定のセルから数値を取得し、Logger.logには各値を反映できていますが、それを配列に格納できません。
もしくは配列でなくとも、そのまま数値を現在開いている一括管理するスプレッドシートの指定の位置にそのままsetValueできれば問題はないです。

しかし、いろいろと調べては試してみるも、何が合っていて、間違っているのか区別がつかず、途方に暮れています。

また動作が少し遅いのでもっとシンプルな文にならないかなと思いながらも、どう改良できるかわかりません。

わからないことだらけで、申し訳ございませんが、どうぞよろしくお願いいたします。

Answer 1

現在の質問だと問題点が見えないです

• 「数値を吸い上げるまではかなった」

Logger.log(latestPaidHour);は動作してないはず。
となるとLogger.log(sheet);が出力をしているはずで、これは配列を出力しているはず(mapの結果なので)
であれば、「Logger.logには各値を反映できていますが、それを配列に格納できません」というのは整合性が取れません。
→出力サンプルを提示いただけませんか。

• 書き込み

これを開いているシートなんだと思いますが、概念的には

SpreadsheetApp.getActiveSheet.getRange(1,1,sheet.length, sheet[0].length).setValues(sheet);

なので、形をあわせるだけ。

• 動作が少し遅いのでもっとシンプルな文

シンプルにしたりmapを組み合わせることでは早める効果は今回の例では薄いと思います。遅い原因は毎回ファイルとフォルダを名前から探したり、各ファイルから値を拾ってきたりするところなので、原理的に大幅な改善が難しいです。
ジャストアイデアですが、↓のような対策をすると改善が期待できると思います。
0. ファイル名の重複がおこらないようなルール(同姓だと重複する)にして、ファイルidの探査をやめる
(余談ですが、getFilesByNameはフォルダ無視して検索してくれるので、もし姓が重複しないなら、DriveApp.getFilesByNameとすることで、現在のコードでもフォルダを無視して取得可能なはず)
0. ファイルidやフォルダidはあらかじめ、管理シートに記録しておく(プログラムを書く)
(こちらは絶対量としてかかる時間はへらないのですが、例えば夜間に時間のかかる処理を逃がしておいて、ユーザが操作する時には応答を速くするという考えです。まあそもそも全体を夜間に処理したらいいのかもしれませんが)

---

今のコードの意味を(多分)破綻させないように縮約してみました。

function paidHolidaySet(){

  //社員を配列データとして格納
  var users = [ 'akito','fujisawa','inoue']; 
  var fileIDs = users.map(function(element) {

    //月末lmld=lastmonthlastday
    var dt = new Date();
    var lmld = new Date(dt.getFullYear(), dt.getMonth(), 0)
    
    // YYYYMMDD形式に変換
    var year_str = lmld.getFullYear();
    var month_str = lmld.getMonth()+ 1; // monthはなんと0-11
    var day_str = lmld.getDate();
    
    var month_str = ('0' + month_str).slice(-2);
    var day_str = ('0' + day_str).slice(-2);
    return "作業月報_" + element + "_" + year_str + month_str + day_str; 
  })
  .map(function(element){
    //フォルダー名をドライブから検索し、IDを取得
    var folderID = DriveApp.getFoldersByName(element).next().getId();
    // Logger.log(folderID);
    return DriveApp.getFolderById(folderID).getFilesByName(element).next().getId();
  });
　Logger.log(fileIDs);

  //スプレッドシートアプリから取得したIDのスプレッドシートを起動し、アクティブシート取得
  var sheet = fileIDs.map(function(element){
    var sht=  SpreadsheetApp.openById(element);
    var st = sht.getActiveSheet();
    var array = [];
    var latestPaidHour = st.getRange( 1,1 ).getValue(); //here getValuesをgetValueに変更
    latestPaidHour = latestPaidHour.toString();
    Logger.log(latestPaidHour);
    return latestPaidHour; //here forの意味がなさそうなのでやめた
  });
 SpreadsheetApp.getActiveSheet.getRange(1,1,1,sheet.length).setValues([sheet]);
 //here 最初の1,1はA1。3つめの1は、どうも1回の操作で横一列しか取らないっぽい。ので固定値。
 Logger.log(sheet);
}

Comments

[amnoskanae]

papinianus 様

早速お返事いただきありがとうございます。
一度配列に格納しようとせず、ただ情報を抜き取れているかどうかチェックするために、以下のように書き換え、得られたログの出力情報はこちらです。

```JavaScript
//スプレッドシートアプリから取得したIDのスプレッドシートを起動し、アクティブシート取得
var sheet = fileIDs.map(function(element){
var sht= SpreadsheetApp.openById(element);
var st = sht.getActiveSheet();
var array = [];
var latestPaidHour = st.getRange( 1,1 ).getValues();
　　 Logger.log(latestPaidHour);
});

```

,,,

> [19-02-04 11:47:53:754 JST] [[a]]
> [19-02-04 11:47:54:339 JST] [[b]]
> [19-02-04 11:47:54:538 JST] [[c]]
> [19-02-04 11:47:54:539 JST] [null, null, null]

また動作を早くすることに対してのアイデア誠にありがとうございます。
おそらく前者で、苗字がかぶらないようにしてあるので、それは問題なくできると思います。
ですので、一度ファイルの検索をなくしてみようと思います。

質問するのも初めてで、わかりづらい質問となってしまったのにも関わらず、ご回答いただきありがとうございます。
引き続き、お力添えいただけると助かります！
よろしくお願いいたします。

[papinianus]

> [19-02-04 11:47:53:754 JST] [[a]]
> [19-02-04 11:47:54:339 JST] [[b]]
> [19-02-04 11:47:54:538 JST] [[c]]
この出力は、求めるものでしょうか？
* 「この出力」というのは、社員ひとりずつについて、1つの値(aやbなど)が取れていくという結果のことです。

[amnoskanae]

すみません。上記は間違えで、今削除リクエストをおくりました。

[amnoskanae]

出力については求めるものです。サンプルの値がその値です。その取れた値を一括管理するスプレッドシートに入力したいです。

[amnoskanae]

また最初の質問にあった通りに記入した際に、得られたログの出力がこちらです。　
19-02-04 11:59:08:305 JST] [1.0, 1.0, 1.0]
（これを先ほど追記としてお送りしようといたしました。使い慣れず、何度も送信してしまい、申し訳ございません。）

[papinianus]

回答を修正しました。
> 出力については求めるものです。
ということは
> 19-02-04 11:59:08:305 JST] [1.0, 1.0, 1.0]
は
> [a, b, c]
であれば求める値であるはずですよね？(aやb,cが1になることもあるのかもしれませんが)
………という方針で回答を修正しています。
(ちなみにコメントにマウスをあわせると鉛筆アイコンがあり、編集可能です。削除リクエストしても多分削除してくれません。このコメントのように時刻の右に「編集」とつきます)

[amnoskanae]

何度もお返事いただき本当に助かります。
無事数値を指定の場所に入れることができました！
https://qiita.com/ShishidoToru/items/0ab9de4ea281df9358f4
こちらも参考にしつつ、やってみました。
papinianus 様のおかげで、新たに多くのことを学べました。この調子で色々と実際にGASなどを動かしながら、プログラミングの勉強に力を入れてまいります。
誠にありがとうございました！

[papinianus]

その引用リンクの件、あとから気付いてsetValues([sheet])と[]をつけました。もし的外れだったら無視してください。

[amnoskanae]

ありがとうございます！
なるほど、変換せずともそのように[]をつけるだけでいいんですね。
シンプルなプログラミング文が書けるように日々邁進します。