I currently have a script that takes the selected option in column F and updates a different sheet with the selected value. The first sheet is set up like this

The other sheets are set to start on the 16th of the month and finish on the 15th of the following month. So the sheet labeled (2023/01) has dates starting from row 7. The dates are in column A and the value to be updated is in column D.

The problem

In the script, the dates are all offset by one. So if you select 23/01/16 it actually matches 23/01/17. This doesn't seem to be a problem except when the 15th of every month is selected. Since the sheets end on the 15th and the dates are offset it matches the 16th but the 16th does not exist on the sheet so the cell is not updated.

MY solution

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);
       }

CodePudding user response：

Please find my explanation in the comments:

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つの日付を入力してください
 


}

To simplify:

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;
        }
      }
    }
  }
}