Question 268510 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Function button not working, only works manual input

Can you please help me why its not working everytime i click on button its not updating. I want to get the old value of column J and column K and set it to column L and M everytime it was change. Please help me TIA.

    function         submitValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet1");
  var sheet2 = ss.getSheetByName("Sheet2");

  // Get the value from C11 on Sheet1
  var cellC11 = sheet1.getRange("C11").getValue();

  // Get the current date and time
  var currentDate = new Date();

  // Set the value of C11 from Sheet1 to Sheet2 in column J and set a new date in column K
  sheet2.getRange("J1").setValue(cellC11);
  sheet2.getRange("K1").setValue(currentDate);
}

function onEdit(e) {
  var sheet = e.source.getSheetByName("Sheet2");
  var range = e.range;

  if (sheet.getName() === "Sheet2") {
    if (range.getColumn() === 10) { // Column J is column 10
      var oldValue = e.oldValue;
      var newValue = e.value;

      if (oldValue !== newValue) {
        // Set the previous value in column L
        sheet.getRange(range.getRow(), 12).setValue(oldValue);
      }
    } else if (range.getColumn() === 11) { // Column K is column 11
      var oldDate = e.oldValue;
      var newDate = e.value;

      if (oldDate !== newDate) {
        // Set the previous date in column M
        sheet.getRange(range.getRow(), 13).setValue(oldDate);
      }
    }
  }
}

I try to get the previous value of column J and Column K and set value to Column L and M. it only works when i manually type the value on the column J.

Tags:

2

Answers


  1. 0 
    function submitValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet1");
  var sheet2 = ss.getSheetByName("Sheet2");

  // Get the value from C11 on Sheet1
  var cellC11 = sheet1.getRange("C11").getValue();

  // Get the current date and time
  var currentDate = new Date();

  // Set the value of C11 from Sheet1 to Sheet2 in column J and set a new date in column K
  sheet2.getRange("J1").setValue(cellC11);
  sheet2.getRange("K1").setValue(currentDate);
}

function onEdit(e) {
  var sheet = e.source.getSheetByName("Sheet2");
  var range = e.range;

  if (sheet.getName() === "Sheet2") {
    if (range.getColumn() === 10) { // Column J is column 10
      var oldValue = e.oldValue;
      var newValue = range.getValue();

      if (oldValue !== newValue) {
        // Set the previous value in column L
        sheet.getRange(range.getRow(), 12).setValue(oldValue);
      }
    } else if (range.getColumn() === 11) { // Column K is column 11
      var oldDate = e.oldValue;
      var newDate = range.getValue();

      if (oldDate !== newDate) {
        // Set the previous date in column M
        sheet.getRange(range.getRow(), 13).setValue(oldDate);
      }
    }
  }
}
    Login or Signup to reply.
  2. 0

    Try this approach. I found it from here. It takes the 5 digit number from the spreadsheet and converts it into the appropriate timestamp for Javascript.

        function onMyDateEdit(e) {
      e.source.toast("Entry");
      Logger.log(JSON.stringify(e));
      const sh = e.range.getSheet();
      if(sh.getName() == "Sheet2" && e.range.columnStart > 9 && e.range.columnStart < 12 && e.range.rowStart > 2) {
        sh.getRange(1,16).setValue(`old: ${e.oldValue},new ${e.value}`);
        e.source.toast("Gate");
        if(e.range.columnStart == 10) {
          e.range.offset(0,2).setValue(e.oldValue);
        } else {
          let ot = new Date(e.oldValue * 86400000 - 2209132800000);
          let odv = new Date(ot.getFullYear(),ot.getMonth(),ot.getDate()).valueOf();
          let dt = new Date(e.value * 86400000 - 2209132800000);
          let ndv = new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
          if(odv != ndv) {
            e.range.offset(0,2).setValue(new Date(dt)).setNumberFormat("MM/dd/yyyy")
          }
        }
      }
    }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search