GAS　転記されていないデータのみ転記させる方法

GAS初心者です。業務上以下のことをしたいと思っています。
他社のgoogleフォームで送信したスプレッドシート内容を自社のスプレッドシートに転記したい。
ただし自社のスプレッドシートは他社には見せたくないため、自社のIDや権限付与はしたくないので、他社のスプシ(A）から自社のスプシ（B)の間にスプシ（C)（ID・権限付与する）を介して転記したいと思っています。
A→Cは自動転記できたのですが、C→Aがうまくいきません。
A→Cに自動転記されたのをトリガー「変更」の設定でC→Bとしたかったのですが、自動転記したものは「変更」が認識されずトリガーとして設定できないようです。
そこで、C→Bには、Cのデータ全ての日時を検索してBの同じ日時のもの（すでに転記したものは）は転記させず、まだ転記していないものをすべて転記するという設定にして、指定時間AM9：00になったら実行するトリガーを設定したいです。

色々調べて似たものを探し（同じくこの質問コーナーにて質問されていたものを借用しました。リンクはこちらです。https://teratail.com/questions/218191）
同じシート上に転記させる設定のものがあ以下のようににアレンジしましたがうまくいきません。
どのように修正すればいいでしょうか。
また、毎日9：00に自動実行させる設定もお願いしたいです。どうかお力を貸してください！

function myFunction11() {
var SS_CopyFrom =SpreadsheetApp.openById("転記元シートID");
var Sheet_CopyFrom = SS_CopyFrom.getSheetByName('フォームの回答');
var LrFrom = Sheet_CopyFrom.getLastRow();
var LcFrom = Sheet_CopyFrom.getLastColumn();
var ValuesFrom = Sheet_CopyFrom.getRange(1, 1,LrFrom,LcFrom).getValues();
var CountFrom = ValuesFrom.length;
var BoxFrom = ""
var Box2From = ""
var resFrom = ""

var SS_CopyTo =SpreadsheetApp.openById("転記先シートID");
var Sheet_CopyTo = SS_CopyTo.getSheetByName('フォームの回答 1');
var LrTo = Sheet_CopyFrom.getLastRow();
var LcTo = Sheet_CopyFrom.getLastColumn();
var ValuesTo = Sheet_CopyTo.getRange(1,6,LrTo,LcTo).getValues();//転記先のG列を取得
var CountTo = ValuesTo.length;
var resTo = ""

for( var i = 0; i < CountFrom; i++ ) {
var found = false;
for( var j = 0; j < CountTo; j++ ) {
if( ValuesFrom[i][0] === ValuesTo[j][6]) {
found = true;
break;
}
}
if( !found )
resTo += ValuesFrom[i];
}
Browser.msgBox(resTo);

}

退会済みユーザー

2022/03/19 05:23 編集

「うまくいきません」とは具体的にどういうことなのでしょうか？実行するとなにかエラーが出るのでしょうか。ならばそのエラーを書いてください。エラーが出ない場合であっても、期待する動作は具体的にどういうものなのか、という点を含めてわかりやすく説明してください。

ienofyan

2022/03/20 05:36 編集

大変申し訳ございません。うまくかない状態と期待する動作について、具体性がなかったです。申し訳ありませんでした。うまくいかない状態は、エラーが出るのではなく、実行され「実行完了」となるのですが、転記させたいシートには何も転記されない、という状態です。（転記元シート＝（C）、転記先シート＝（B）です。）期待する動作が、転記元（C)のA列(日時）の2行目から最終行までのすべてのデータが、転記先（B）のすでにあるデータのG列（日時）の中にあるかないかを検索し、同じ日時があればスルーし、なければ、ないものすべてを最終行に転記をさせる、というものです。ですが、実行が完了されても、同じ「日時」がないにもかかわらず一つも転記されない、という状況です。どうぞ宜しくお願いいたします。

行動規範の内容に同意します

回答2件

ありがとうございます！！！！
出来ました！これです！こんなことをしたかったんです！まさにこれです！
数週間ずっと悩み続けていたことが実現し、本当に感動です！！
しかも、すごく分かりやすいです（＞＜）！！
本当に、本当にありがとうございました！！！
トリガーに関しても参考URLまでつけていただき、ありがとうございます！
参考にさせて頂きます！

2022.3.21追記【→解決しました。もうしわけありません。原因は、行の最終行の下に行がない状態だと転記先が見つからないためかエラーになったようです。。
行を追加して空白の行を作成したら実行完了しました。お騒がせしました。】
すみません、少し困った状況になりましたので再度ご質問追加させて頂いております。申し訳ありません。
昨日、毎日9：00に実行されるよう以下の通り追加設定し、翌日9：00以降に確認しましたら、エラーとなっていて、転記されていませんでした。。
エラー内容は、
12:21:01 エラー
Exception: Invalid argument
myFunction11 @ 毎日9時に自動転記.gs:9
でした。
※「// 翌日の9:00にトリガーを設定」以降を追加したのみです。ただ、本日改めて追加部分(時間設定部分）を削除し実行させてみたら、同じようにエラーメッセージが出てしまいました。何か触ってしまったかと思い、新しくファイルを作り同じように実行してみましたが、エラーでした。昨日は実行完了になっていたのですが、本日はエラーになったのは何か理由があるのでしょうか。。。
設定した内容は以下の通りです。
function myFunction11() {
var SS_CopyFrom = SpreadsheetApp.openById("転記元シートID");
var Sheet_CopyFrom = SS_CopyFrom.getSheetByName('フォームの回答');
var LrFrom = Sheet_CopyFrom.getRange(Sheet_CopyFrom.getLastRow() + 1, 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
var LcFrom = Sheet_CopyFrom.getLastColumn();
var ValuesFrom = Sheet_CopyFrom.getRange(2, 1, LrFrom - 1, LcFrom).getDisplayValues();
var SS_CopyTo = SpreadsheetApp.openById("転記先シートID");
var Sheet_CopyTo = SS_CopyTo.getSheetByName('フォームの回答 1');
var LrTo = Sheet_CopyTo.getRange(Sheet_CopyTo.getLastRow() + 1, 7).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
var LcTo = Sheet_CopyTo.getLastColumn();
var ValuesTo = Sheet_CopyTo.getRange(1, 7, LrTo, LcTo).getDisplayValues();//転記先のG列を取得
var mapTo = ValuesTo.map(e => e[0]);
var ret = ValuesFrom.filter(e => !(mapTo.includes(e[0])));
if (!ret || ret.length < 1) return;
Sheet_CopyTo.getRange(LrTo + 1, 7, ret.length, ret[0].length).setValues(ret);
}

function setTrigger(func_name) {
// 指定のトリガーがあれば削除する。
var triggers = ScriptApp.getProjectTriggers();
for(var i = 0; i < triggers.length; i++) {
if(triggers[i].getHandlerFunction() == func_name)
ScriptApp.deleteTrigger(triggers[i]);
}

// 翌日の9:00にトリガーを設定
var trigger_setTime = new Date();
trigger_setTime.setDate(trigger_setTime.getDate() + 1);
trigger_setTime.setHours(9, 0, 0, 0);
ScriptApp.newTrigger(func_name).timeBased().at(trigger_setTime).create();
}

投稿2022/03/20 11:23

編集2022/03/21 05:21

ienofyan

総合スコア6

ベストアンサー

コメントの御回答から、要件は下記のようなものだと理解しました。間違っている部分があればご指摘ください。
【前提】
下記の2つのシートがあるとします。

①転記元シート（C）
Ａ列２行目以降に日時が記載されている。（フォームの回答データのタイムスタンプ）

②転記先シート（Ｂ）
転記済みのデータが記載されている。Ｇ列が日時。Ｈ列以降は転記データ

【要件】
各シートが上の状態であったとして、
・転記元シートのA列(日時)の2行目から最終行までのすべての日時について、転記先のG列の中にあるか検索
→ 転記先シートのＧ列に同じ日時があればスルー。
転記先シートのＧ列に同じ日時がなければ、転記元の回答データを、転記先シートの転記前最終行の続きに転記する。

【期待する結果の例】
上図の場合、
・転記元シートのＡ２セルと転記先シートのＧ２セルは同じなので転記不要。
・転記元シートのＡ３セル以降は転記先シートのＧ列にないので、転記元シートの３行目以降のデータを転記先シートに転記する。

注意：下記コードが正常に動く前提
転記元シートのA列・転記先シートのG列どちらも全く同じ表示形式（日付時刻フォーマット）である必要があります。

js
1function myFunction11() {
2  var SS_CopyFrom = SpreadsheetApp.openById("転記元シートID");
3  var Sheet_CopyFrom = SS_CopyFrom.getSheetByName('フォームの回答');
4  var LrFrom = Sheet_CopyFrom.getRange(Sheet_CopyFrom.getLastRow() + 1, 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
5  var LcFrom = Sheet_CopyFrom.getLastColumn();
6  var ValuesFrom = Sheet_CopyFrom.getRange(2, 1, LrFrom - 1, LcFrom).getDisplayValues();
7  var SS_CopyTo = SpreadsheetApp.openById("転記先シートID");
8  var Sheet_CopyTo = SS_CopyTo.getSheetByName('フォームの回答 1');
9  var LrTo = Sheet_CopyTo.getRange(Sheet_CopyTo.getLastRow() + 1, 7).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
10  var LcTo = Sheet_CopyTo.getLastColumn();
11  var ValuesTo = Sheet_CopyTo.getRange(1, 7, LrTo, LcTo).getDisplayValues();//転記先のG列を取得
12  var mapTo = ValuesTo.map(e => e[0]);
13  var ret = ValuesFrom.filter(e => !(mapTo.includes(e[0])));
14  if (!ret || ret.length < 1) return;
15  Sheet_CopyTo.getRange(LrTo + 1, 7, ret.length, ret[0].length).setValues(ret);
16}