我目前有一個腳本，它采用 F 列中的選定選項并使用選定值更新不同的作業表。第一張表是這樣設定的
| 一種 | 乙 | C | D | 乙 | F |
|---|---|---|---|---|---|
| 日期 | 其他作業表的名稱 | 月 | 日 | 作業日 | 選項 |
其他作業表將在本月的 16 日開始，并在下個月的 15 日結束。因此，標簽為 (2023/01) 的作業表的日期從第 7 行開始。日期在 A 列中，要更新的值在 D 列中。
問題
在腳本中，日期都偏移一。因此，如果您選擇 23/01/16，它實際上匹配 23/01/17。這似乎不是問題，除非選擇每個月的 15 日。由于作業表在 15 日結束并且日期偏移，因此它與 16 日匹配，但作業表上不存在 16 日，因此不會更新單元格。
我的解決方案
To fix this issue I created a condition to check if the selected date includes '/16' then run some code. I then created a variable to get the previous sheet and got the last row and set the value that way. I used toast() to check if the values I am selecting are correct and they seem to be. The sheet name is correct and the last row is correct but I am not actually seeing the cell being updated. I am not sure what I am doing wrong so any help would be greatly appreciated.
/* 休日入力イベント
--------------------------------------*/
function changeHoliday(ss) {
var sheet = ss.getActiveSheet(); //アクティブなシート
var sheetName = sheet.getSheetName();
var atvc = sheet.getActiveCell(); //アクティブセル
//休日シートの休日を変更した時だけ
if(sheetName=='休日' || sheetName && atvc.getColumn() == 6){
var flag = atvc.getValue(); //休日かどうか
var targetSheetName = String(atvc.offset(1, -4).getValue()); //対応するシート名
//Get previous sheet name
var prevSheetName = String(atvc.offset(-1,-4).getValue());
var targetDate = Utilities.formatDate(atvc.offset(1, -5).getValue(),"JST", "yyyy/MM/dd"); //対応する日付
// var targetDateEndofSheet = Utilities.formatDate(atvc.offset(0, -5).getValue(),"JST", "yyyy/MM/dd");
var targetSheet = ss.getSheetByName(targetSheetName);
var lastRow = targetSheet.getLastRow();
var values = targetSheet.getRange(1,1,lastRow,1).getValues();
// 取得したデータから一致する日付を探す
//original i=7
for (var i=7; i<lastRow; i ){
var d = Utilities.formatDate(values[i][0],"JST", "yyyy/MM/dd");
//My if statement
if(targetDate.includes("/16")) {
var targetS = ss.getSheetByName(prevSheetName); //get the pervious sheet
var lastR = targetS.getLastRow(); //get the last row of the previous sheet
//check the values
ss.toast( "prev sheet name " prevSheetName "last r: " lastR "flag" flag "td " targetDate)
//select the cell 4 of the last row
var r = prevSheetName.getRange(lastR,4);
r.setValue(flag); //set the select value
}
if(d == targetDate){
var range = targetSheet.getRange(i,4);
// データ追加
range.setValue(flag);
}
}
}
//一度に1つの日付を入力してください
}
/* 休日の保護の解除
--------------------------------------*/
function protectionRemove_(targetDate){
var ss = SpreadsheetApp.getActive();
var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var i = 0; i < protections.length; i ) {
var protection = protections[i];
// 説明文が一致したら削除
if (protection.getDescription() == targetDate) {
protection.remove();
}
}
}
I was able to solve it with some of the suggestions made in the comments
I added this to my code
if(d.includes("/16") && targetDate.includes("/16")) {
var prevSheetName = String(atvc.offset(-1, -4).getValue()); //get target page
var targetSheet = ss.getSheetByName(prevSheetName);
var lr= targetSheet.getLastRow(); //select the last of the target page
var r = targetSheet.getRange(lr,4); //set the range
r.setValue(flag);
}
uj5u.com熱心網友回復：
請在評論中找到我的解釋：
function changeHoliday(ss) {
var sheet = ss.getActiveSheet(); //アクティブなシート
var sheetName = sheet.getSheetName();
var atvc = sheet.getActiveCell(); //アクティブセル
//休日シートの休日を変更した時だけ
/* Should be AND OPERATOR */
if(sheetName=='休日' && sheetName && atvc.getColumn() == 6){
var flag = atvc.getValue(); //休日かどうか
/* Should check the value */
if (flag == '休日') {
/* Row offset should be 0 */
var targetSheetName = String(atvc.offset(0, -4).getValue()); //対応するシート名
//Get previous sheet name
/* Not needed */
// var prevSheetName = String(atvc.offset(-1,-4).getValue());
/* Row offset should be 0 */
var targetDate = Utilities.formatDate(atvc.offset(0, -5).getValue(),"JST", "yyyy/MM/dd"); //対応する日付
// var targetDateEndofSheet = Utilities.formatDate(atvc.offset(0, -5).getValue(),"JST", "yyyy/MM/dd");
var targetSheet = ss.getSheetByName(targetSheetName);
var lastRow = targetSheet.getLastRow();
var values = targetSheet.getRange(1,1,lastRow,1).getValues();
// 取得したデータから一致する日付を探す
//original i=7
/* i should starts from 6 */
for (var i=6; i<lastRow; i ){
var d = Utilities.formatDate(values[i][0],"JST", "yyyy/MM/dd");
//My if statement
/* Not needed */
/*
if(targetDate.includes("/16")) {
var targetS = ss.getSheetByName(prevSheetName); //get the pervious sheet
var lastR = targetS.getLastRow(); //get the last row of the previous sheet
//check the values
ss.toast( "prev sheet name " prevSheetName "last r: " lastR "flag" flag "td " targetDate)
//select the cell 4 of the last row
var r = prevSheetName.getRange(lastR,4);
r.setValue(flag); //set the select value
}
*/
if(d == targetDate){
/* Row should be i 1 */
var range = targetSheet.getRange(i 1,4);
// データ追加
range.setValue(flag);
/* Better to break */
break;
}
}
}
}
//一度に1つの日付を入力してください
}
為了簡化：
function changeHoliday(ss) {
var sheet = ss.getActiveSheet();
var sheetName = sheet.getSheetName();
var atvc = sheet.getActiveCell();
if(sheetName=='休日' && sheetName && atvc.getColumn() == 6){
var flag = atvc.getValue();
if (flag == '休日') {
var targetSheetName = String(atvc.offset(0, -4).getValue());
var targetDate = Utilities.formatDate(atvc.offset(0, -5).getValue(),"JST", "yyyy/MM/dd"); //対応する日付
var targetSheet = ss.getSheetByName(targetSheetName);
var lastRow = targetSheet.getLastRow();
var values = targetSheet.getRange(1,1,lastRow,1).getValues();
for (var i=6; i<lastRow; i ){
var d = Utilities.formatDate(values[i][0],"JST", "yyyy/MM/dd");
if(d == targetDate){
var range = targetSheet.getRange(i 1,4);
range.setValue(flag);
break;
}
}
}
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/376109.html
標籤：google-apps-script google-sheets