I am completely new to App Script. I have been tasked with updating some code someone else wrote. Here is my problem.

The main sheet contains dates. Row 1 is a year/month/day. Row 2 lists the sheets that each day belongs to for example 2020/1. Row 3 is the month, Row 4 is the day (number) and row 5 is the day (weekday). In Row 6 the user can select 3 options(day off, working day, half-day). If any option is selected the corresponding sheet will be updated.

So for example, if on 2020/1/16 day off is selected the spreadsheet with the name "2020/1" will be updated. The "2020/1" sheet has a column for working days the cell for the row 2020/1/16 will be updated to say "working day". This part of the code works fine. What I wanted to do is change the value in the "2020/1" and have it update in the main sheet. So for example if 2020/1/16 in sheet name "2020/1" is changed to "working day" I want the main sheet to update. Right now it only updates from the main sheet but not the other way around.

I tried updating it using the onEdit function,but I am unsure about how it works. I am seeking any onEdit function examples that check if an edit is made in one tab and updates the cell in a different tab with that value. Since you can't console log or Logger.log the data in the on edit I am having a difficult time trying to figure this out.

screenshots:

https://photos.app.goo.gl/3hjjMrUXfctsnMk69

https://photos.app.goo.gl/tnNue4NTgKM1cb2u8

This is my function so far:

  var sheet = ss.getActiveSheet(); //アクティブなシート
  var sheetName = sheet.getSheetName();
  var atvc = sheet.getActiveCell(); //アクティブセル

  //休日シートの休日を変更した時だけ
  if(sheetName !=='休日' || sheetName && atvc.getColumn() == 4){
    var flag = atvc.getValue(); //休日かどうか
    // var targetSheetName = String(atvc.offset(1, -4).getValue()); //対応するシート名
    // var targetSheetName = "休日"
    var targetDate = Utilities.formatDate(atvc.offset(1, -3).getValue(),"JST", "yyyy/MM/dd"); //対応する日付
    var targetSheet = ss.getSheetByName("休日");
    var lastRow = targetSheet.getLastRow();
    var values = targetSheet.getRange(1,1,lastRow,1).getValues();
    // 取得したデータから一致する日付を探す
    for (var i=7; i<lastRow; i  ){
      var d =  Utilities.formatDate(values[i][0],"JST", "yyyy/MM/dd");

      // 一致したら状態を更新
      if(d == targetDate){
        var range = targetSheet.getRange(i,6);
        // データ追加
        range.setValue(flag);
      }
    }
  }
}

CodePudding user response：

Basically it can be done with this function:

function onEdit(e) {
  if (e.range.columnStart != 4) return;

  var ss = e.source;  
  var active_sheet = ss.getActiveSheet();
  if (active_sheet.getName() == 'Sheet1') return;
  
  var date = active_sheet.getRange(e.range.rowStart,1).getDisplayValue();

  var dest_sheet = ss.getSheetByName('Sheet1');
  var all_dates = dest_sheet.getRange('A:A').getDisplayValues().flat();
  var dest_row = all_dates.indexOf(date) 1;

  dest_sheet.getRange(dest_row,6).setValue(e.value);

  ss.toast('The day '   date   ' has been changed to '   e.value);
}

Whenever you change something in column 4 (D) on any sheet except the sheet 'Sheet1' it will copy the value of the changed cell to the 'Sheet1' in the column 6 (F) on the row that has the same date (in column 1 (A)).