【GAS】スプレッドシートに注文内容を追加するライブラリ

はじめに

まず、楽天市場、Amazon、GooglePlayなどのネットショップはとても便利なので、使わない人はいないのではないかと思います。

そのため、こんなことがあるのではないでしょうか。

ネットショップがあまりに便利なものだから、いろいろ購入すると、いつ、どこで、いくらで買ったのかわからなくなる。

確かに、それぞれのサイトで過去の注文リストを確認することはできますが、できれば自動的にExcelの表などで一括して整理できないものかと考えたことが一度はあると思います。すると、確認のために各サイトを巡る必要がなくなるので、手間が省けますよね。

そして、

手動で購入リスト作成なんて考えたくない。

わかります。

ここでは、この課題を解決するための一つの方法を提案しています。

今回作成するライブラリは、下記項目を与えたらGoogleスプレッドシートの最下行に情報を追加します。

ECサイト名（楽天市場、Amazon、GooglePlay、etc）
商品名
価格
数量
支払額
購入日
購入ショップ名

このライブラリだけでは、購入商品リストを自動構築することはできません。

Gmailの注文確認メールを解析し、スプレッドシートに追加する項目を抽出するところまでは、各ECサイトごとに作成する必要があります。

その後、抽出した注文情報をここで説明するライブラリの関数に渡すことにより、スプレッドシートに項目を追加することができるようになります。

楽天市場の注文確認メールから購入リストの自動作成についての記事を起こしていますので、下記参照ください。

関谷さんのブログ

2021.11.10

【GAS】楽天市場注文リストの自動生成

https://sekiya-san.com/rakuten-order-list-automatic-generation

はじめにねえ、おとん、楽天市場のお買い物マラソンでたくさん注文したけど、何を注文したか忘れちゃうんだけど。いくら購入したのかいちいち調べるのも面倒なんだけど。そお？おとんも結構忘れるよ。でも、注文リストをスプレッドシートに自動的追加してるから忘れても大丈夫なの。購入金額も記録しているから、合計はすぐ出るんだよ。そんなことできるの？ちょっとなるの環境でも設定してくれる？ということで、楽天市場でお買い物をした時に、購入日、購入品、購入価格等の一覧を自動的に作る仕組みを作りました。対象読者ここで紹...

対象読者

ここで紹介する内容は、以下の条件を満たす方が対象となります。

対象読者

楽天市場、Amazon、GooglePlayなどのネットショップで注文することがある
注文内容確認メールをGmailで受け取っている
注文内容をスプレッドシートに記録したい

では、内容を見ていきましょう。

スプレッドシートへの注文内容追加処理

ここでは、スプレッドシートにアクセスして注文内容を追加する関数をライブラリとして公開するところまでを説明します。

注文内容を追加する関数の処理の流れは以下のようになります。

スプレッドシートファイルを作成する
作成したスプレッドシートを開く
初めて開く場合は項目ヘッダをつける
項目を追加する

スプレッドシート作成

ファイル名とフォルダ名を渡し、新規スプレッドシートを作成します。

/*
 * 新規スプレッドシート作成
 */
function createSpreadSheet_(fileName, folderName) {

  // 新規スプレッドシート作成
  var ss = SpreadsheetApp.create(fileName);
  var org = DriveApp.getFileById(ss.getId());

  return org;
}

フォルダ名を渡していますが使っていません。そのため、この関数を使うと常にルートにfilenameのスプレッドシートが作られます。

スプレッドシートを開く

作成したスプレッドシートを開き、最初のシートオブジェクトを返します。

/*
 * スプレッドシートファイルを開き、最初のシートを選択する
 */
function openSpreadSheet_(ss){

    var ssId = ss.getId();
    var ssFile = SpreadsheetApp.openById(ssId);
    var activeSpreadSheet = SpreadsheetApp.setActiveSpreadsheet(ssFile);
    var masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

    return masterSheet;
}

スプレッドシートの最初のシートに注文内容を追記することとします。

項目ヘッダをつける

初めてスプレッドシートを作成する場合は何も書かれていないので、項目ヘッダを出力します。

/*
 * スプレッドシートにヘッダをつける
 */
function initializeSpreadSheet_(masterSheet){
  masterSheet.appendRow([
      'No', '購入日', 'ECサイト', '商品', '値段', '数量', '送料', 
      'ポイント使用', 'クーポン使用', '支払額', '獲得ポイント', 'ショップ名']);
}

値の取得と設定

値の取得と設定は以下のgetValue_()、setValue_()関数で行います。

sheetで指定されるシートの行(row)と列(col)を指定し、値の取得と設定が行えます。

/*
 * スプレッドシートsheetのrow, colから値取得
 */
function getValue_(sheet,row,col) {
  var value = sheet.getRange(row, col).getValue();
  return value;
}

/*
 * スプレッドシートsheetのrow, colに値設定
 */
function setValue_(sheet,row,col,value){
  var cell = sheet.getRange(row,col);
  cell.setValue(value);
}

項目追加

項目を追加する際には、以下のようにスプレッドシートを開いた後に、

  if(fileExists_(fileName, folderName)){
    var files = DriveApp.getRootFolder().getFilesByName(fileName); 
    ss = files.next();
    masterSheet = openSpreadSheet_(ss);
  }
  else{
    ss = createSpreadSheet_(fileName, folderName);
    masterSheet = openSpreadSheet_(ss);
    initializeSpreadSheet_(masterSheet);  
  }

以下のようにappendRow()に追加したいデータを渡すと、最終行の次の行にデータが書き込まれます。

  // スプレッドシートの最終行に追加
  masterSheet.appendRow([
        no-1, orderDate, ecSite, item, price, count, 0, 0, 0, 0, 0, shop]);

最終行に情報を追記する場合は、以下のようにsetValue_()を使います。

no-1で指定される行が最終行を指します。

  // スプレッドシートの最終行に追加
  setValue_(masterSheet, no-1, 7, shipping);
  setValue_(masterSheet, no-1, 8, point_used);
  setValue_(masterSheet, no-1, 9, coupon_used);
  setValue_(masterSheet, no-1, 10, payment);
  setValue_(masterSheet, no-1, 11, point_get);

例として、スプレッドシートに注文内容が追加された表示内容を以下に示します。

全てのコード

/*
 * libAddOrderToList
 * 
 * 注文情報をスプレッドシートに追記するライブラリ
 * 
 * 2021/10/27 created by N.Sekiya
 */

function test(){
  registOderInfoToSpreadSheet(
    'test', '',
    new Date(),
    '楽天市場',
    '【クーポン利用で21980円】高コスパ 10.1インチ Android10.0 8コア SIMフリー 4G LTE通信 大画面',
    23980,
    1,
    21980,
    '東京博海　楽天市場店'
  );
}

function registOderInfoToSpreadSheet(fileName, folderName,
  orderDate, ecSite, item, price, count, shop){

  Logger.log('orderDate=' + orderDate + ' ec=' + ecSite);
  Logger.log('item=' + item);
  Logger.log('price=' + price + ' cnt=' + count + ' shop=' + shop);
  
  var ss = null;
  if(fileExists_(fileName, folderName)){
    var files = DriveApp.getRootFolder().getFilesByName(fileName); 
    ss = files.next();
    masterSheet = openSpreadSheet_(ss);
  }
  else{
    ss = createSpreadSheet_(fileName, folderName);
    masterSheet = openSpreadSheet_(ss);
    initializeSpreadSheet_(masterSheet);  
  }

  var no = 1;
  var val = getValue_(masterSheet, no, 1);
  while(val != ''){
    no++;
    val = getValue_(masterSheet, no, 1);
  }

  // スプレッドシートの最終行に追加
  masterSheet.appendRow([
        no-1, orderDate, ecSite, item, price, count, 0, 0, 0, 0, 0, shop]);
}

function registOderInfoToSpreadSheetSub(
  fileName, folderName, shipping, point_used, coupon_used, payment, point_get){
  
  Logger.log('shipping=' + shipping + ' point_used=' + point_used + 
  ' coupon_used=' + coupon_used + ' payment=' + payment + ' point_get=' + point_get);

  var ss = null;
  if(fileExists_(fileName, folderName)){
    var files = DriveApp.getRootFolder().getFilesByName(fileName); 
    ss = files.next();
    masterSheet = openSpreadSheet_(ss);
  }
  else{
    return;   // fileNameのファイルがない場合は処理しない
  }

  var no = 1;
  var val = getValue_(masterSheet, no, 1);
  while(val != ''){
    no++;
    val = getValue_(masterSheet, no, 1);
  }

  // スプレッドシートの最終行に追加
  setValue_(masterSheet, no-1, 7, shipping);
  setValue_(masterSheet, no-1, 8, point_used);
  setValue_(masterSheet, no-1, 9, coupon_used);
  setValue_(masterSheet, no-1, 10, payment);
  setValue_(masterSheet, no-1, 11, point_get);
}

/*
 * 新規スプレッドシート作成
 */
function createSpreadSheet_(fileName, folderName) {

  // 新規スプレッドシート作成
  var ss = SpreadsheetApp.create(fileName);
  var org = DriveApp.getFileById(ss.getId());

  return org;
}

/*
 * スプレッドシートファイルを開き、最初のシートを選択する
 */
function openSpreadSheet_(ss){

    var ssId = ss.getId();
    var ssFile = SpreadsheetApp.openById(ssId);
    var activeSpreadSheet = SpreadsheetApp.setActiveSpreadsheet(ssFile);
    var masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

    return masterSheet;
}

/*
 * スプレッドシートにヘッダをつける
 */
function initializeSpreadSheet_(masterSheet){
  masterSheet.appendRow([
      'No', '購入日', 'ECサイト', '商品', '値段', '数量', '送料', 
      'ポイント使用', 'クーポン使用', '支払額', '獲得ポイント', 'ショップ名']);
}

/*
 * ファイル存在チェック
 */
function fileExists_(fileName, folderName) {
  var files = DriveApp.getRootFolder().getFilesByName(fileName);
  
  while (files.hasNext()) {
    var file = files.next();
    if(fileName == file.getName()) {
      return true;
    }
  }
  return false;
}

/*
 * スプレッドシートsheetのrow, colから値取得
 */
function getValue_(sheet,row,col) {
  var value = sheet.getRange(row, col).getValue();
  return value;
}

/*
 * スプレッドシートsheetのrow, colに値設定
 */
function setValue_(sheet,row,col,value){
  var cell = sheet.getRange(row,col);
  cell.setValue(value);
}

このコードをlibAddOrderToListという名前にして、ライブラリとして使用します。

ライブラリについては以下の記事を確認ください。

関谷さんのブログ

2021.10.31