【GAS】Google Apps Script 活用事例　SUMIF関数を組みやすいようにGoogle Formsのデータを、項目別に整形し直す方法

現在、実務で備品の在庫管理するツールをGASとスプレッドシートで作っています。前回の記事で、Google Formsのプルダウンをスプレッドシートから作成する方法をまとめました。

実際にスプレッドシートと連携してGoogle Formsのデータを見てみると、こんな感じ。必要項目も、部署も記入されていて、集計も出来るし、グラフも作れるので、目も当てられない、杜撰な管理状態からは、一歩前進したわけですが、直感的に把握しやすい形へGASで変換出来ないかなと思いました。

後日談ですが、フォームのデータは、上記のように集計が出来ます。FormsのRAWデータのままだとSUMIFで集計が出来ないと勘違いし、GASを組んでしまいました。

ソースコードはこんな感じ

//FormsのデータをSUMIFしやすくなるようにデータを整形する。
function inventoryCalculation() {
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 const sourceSheet  = spreadsheet.getSheetByName('Forms');
 
 const sourceValues = sourceSheet.getDataRange().getValues();
 const headerRow    = sourceValues[0];
 
 console.log(headerRow);
 
 //将来的に列が足されたりしても動くように、見出しを探して求める。
 const columnNumber = {
   
   medicine1 : '風邪薬',
   medicine2 : '頭痛薬',
   medicine3 : 'バンドエイド',
   medicine4 : '整腸剤',
   
 }
 
 const medicine1IdxColumn  = headerRow.indexOf(columnNumber.medicine1);//風邪薬
 const medicine2IdxColumn  = headerRow.indexOf(columnNumber.medicine2);//頭痛薬
 const medicine3IdxColumn  = headerRow.indexOf(columnNumber.medicine3);//バンドエイド
 const medicine4IdxColumn  = headerRow.indexOf(columnNumber.medicine4);//整腸剤
 
 console.log(`風邪薬　配列番号　${medicine1IdxColumn}`);
 
 
 const cold          = sourceValues[0][medicine1IdxColumn];
 const headache      = sourceValues[0][medicine2IdxColumn];
 const bandaid       = sourceValues[0][medicine3IdxColumn];
 const stomach       = sourceValues[0][medicine4IdxColumn];
 
 //備品リストなど
 const categories    = [cold, headache, bandaid, stomach];
 let targetArray     = [];
 
 
 //部署の詳細
 const sectionList    = ['ニューヨーク','秋葉原','ロンドン','パリ','ジュネーブ'];

 
 //Google Formsの選択肢の内容
 const formsQuestion     = ['出庫','入庫','棚卸'];
 
 //日付の配列
 const monthList         = ['2020/06/01','2020/07/01'];
 
 //1次元配列 records
 //各セル　element
 //Google Formsの内容を入れ替えて、集計がしやすいように配列を操作している。
 for(const records of sourceValues){
   //初期化
   let index       = 0;
   let section     = '';
   let question    = '';
   let date        = '';    
   
   for(const element of records){
     //console.log(element);
     
     if(sectionList.indexOf(element) !== -1){
       section += element;
     }//if
     
     if(formsQuestion.indexOf(element) !== -1){
       question   += element;
     }//if
     
     if(monthList.indexOf(element) !== -1){
       date       += element;
       console.log(date);
     }
     //もし、セルが数値だったら、
     if(typeof element == 'number'){
       
       const category   = categories[index];
       targetArray.push([date, category, section, question, element]);
       
       index += 1;
     }//if
   }//for
 }//for
 console.log(targetArray);
 return targetArray;
}//end


function setValues() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('在庫集計表');
 
 //全て削除して、Formsのデータを全て変換
 sheet.getRange('A2:E').clearContent();
 
 const FormsValues = inventoryCalculation();
 
 
 sheet.getRange(2, 1, FormsValues.length, FormsValues[0].length).setValues(FormsValues);
 
}//end

indexOfで見出し行の検索で使うと便利。

headerRow.indexOf(columnNumber.medicine1);

headerRowは、見出し行を取得しており、indexOfで任意の列を探すスクリプトは、仕様が完全に決まっていない状態や、将来的に列が増える可能性がある場合に、とても有効です。ソースコードが長くなってしまう欠点がありますが.....。風邪薬は、E列、5列目、配列番号は4と返ってきます。

この長いスクリプト、実行するとどうなるの？

実行すると、Formsのデータを整形して、下記のように整形します。無駄に複雑な事をした結果、データは分かりやすく、見やすくはなりました。なので、細かい事は気にしない事にします。

どうしても必要かと言われると、そうでは無いのですが、別のどこかで、応用が出来そうです。ちなみに、上記フォーマットだと、ニューヨークのデータ入力をする際、風邪薬と、頭痛薬の2種類の使用があった場合、2回入力しないといけないため、大変です。

POSレジなど、商品を1点ずつバーコードで、読むタイプのデータって、割とこんな感じで登録されているんじゃないかなと思います。

実務では、このように配列操作をして、新しいシートに書き出して、Filter関数で、その当月のデータのみを表示させています。

おまけ、Google Formsの選択肢を取得する方法

今回のスクリプトは、分かりやすくするために配列内容を静的に書いていますが、実際は、Google Formsの選択肢を配列で取得しています。そうする事により、Google Formsを修正した場合に、スクリプトを編集せずに済みます。GASを弄れる人がチーム内に僕だけという状況なので、他の人が極力ソースコードを弄る必要が無いように配慮した書き方が出来ると親切かなと思います。

 //Google Formsの選択肢の内容
 const formsQuestion     = ['出庫','入庫','棚卸'];

//Google Formsの質問項目を取得
function getFormsItems() {
const form   = FormApp.openById('**************');
const items  = form.getItems();

let question = [];

for(const item of items){
  
  const itemName = item.getTitle();
  const itemId   = item.getId();
  
  console.log(`質問名 ${itemName}, \n質問のID ${itemId}`);
  
  //itemIdは数字が入る
  if(itemId === 123456789){
    const choices = item.asMultipleChoiceItem().getChoices();
    
    console.log(itemName);
    console.log(choices);
    
    
    for(const choice of choices){
      const value = choice.getValue();
      question.push(value);
      
      console.log(value);
    }//for of
  }//if
}//for
console.log(question);
return question;
}//end

ハマったのは、この部分。

const choices = item.asMultipleChoiceItem().getChoices();

Google Formsの質問タイプが、ラジオボタンになっているか確認してください。プルダウンだったり、チェックボックスだったりで、使用するメソッドが変わってきます。

Class MultipleChoiceItem  |  Apps Script  |  Google Developers

const value = choice.getValue();

あと、上記もずいぶん、ハマりました。getTextとか、getTitleではなく、まさかの、getValue()　なんか、こういうところ、スッキリしないです.....。