Skip to content
The Chat Archive
gpt-4-0314· OpenAI WildChat-4.8M (AI2)

can you fix the error in the following code function onEdit(e) { addTimestamp1(e);…

  1. User

    can you fix the error in the following code
    function onEdit(e) {

    addTimestamp1(e); addTimestamp2(e); addTimestamp3(e); addTimestamp4(e); addTimestamp5(e); addTimestamp6(e); addTimestamp7(e); addTimestamp8(e); addTimestamp9(e); }

    function addTimestamp1(e) { var startRow = 7; var ws = "جدول المتابعة"; var row = e.range.getRow(); var col = e.range.getColumn(); var sheet = e.source.getActiveSheet(); var currentDate = new Date();

    if (sheet.getName() === ws && row >= startRow) { var excludedColumns = [10, 18, 26]; if (excludedColumns.indexOf(col) === -1) { var letter = getLetter(col); if (sheet.getRange(letter + "5").getValue() == "") { sheet.getRange(letter + "5").setValue(currentDate); } } } }

    function getLetter(col) { if (col >= 1 && col <= 26) { return String.fromCharCode(64 + col); } else if (col > 26 && col <= 702) { var firstLetter = String.fromCharCode(64 + parseInt((col - 1) / 26)); var secondLetter = String.fromCharCode(65 + ((col - 1) % 26)); return firstLetter + secondLetter; } else { throw new Error("Column out of range"); } }

    function addTimestamp2(e) { const timestampCols = [ { col: 3, cell: "C5" },{ col: 5, cell: "E5" },{ col: 7, cell: "G5" },{ col: 9, cell: "I5" },{ col: 11, cell: "K5" },{ col: 13, cell: "M5" },{ col: 15, cell: "O5" },{ col: 17, cell: "Q5" },{ col: 19, cell: "S5" },{ col: 21, cell: "U5" } ];

    const ws = "اخراج العيون - الملح"; const row = e.range.getRow(); const col = e.range.getColumn();

    if (e.source.getActiveSheet().getName() === ws) { const currentDate = new Date(); timestampCols.forEach(function (timestampCol) { if (col === timestampCol.col && row >= 6) { const cellValue = e.source.getActiveSheet().getRange(timestampCol.cell).getValue(); if (cellValue === "") { e.source.getActiveSheet().getRange(timestampCol.cell).setValue(currentDate); }} }); }}

    function addTimestamp3(e) { const timestampCols = [ { col: 3, cell: "C4" },{ col: 5, cell: "E4" },{ col: 7, cell: "G4" },{ col: 9, cell: "I4" },{ col: 11, cell: "K4" },{ col: 13, cell: "M4" },{ col: 15, cell: "O4" } ];

    const ws = "المطعوم - السنامكة"; const row = e.range.getRow(); const col = e.range.getColumn();

    if (e.source.getActiveSheet().getName() === ws) { const currentDate = new Date(); timestampCols.forEach(function (timestampCol) { if (col === timestampCol.col && row >= 5) { const cellValue = e.source.getActiveSheet().getRange(timestampCol.cell).getValue(); if (cellValue === "") { e.source.getActiveSheet().getRange(timestampCol.cell).setValue(currentDate); }} }); }}

    function addTimestamp4(e) { const timestampCols = [ { col: 2, cell: "B4" },{ col: 4, cell: "D4" },{ col: 6, cell: "F4" },{ col: 8, cell: "H4" },{ col: 10, cell: "J4" },{ col: 12, cell: "L4" },{ col: 14, cell: "N4" },{ col: 16, cell: "P4" },{ col: 18, cell: "R4" },{ col: 20, cell: "T4" } ];

    const ws = "المشموم - القسط الهندي"; const row = e.range.getRow(); const col = e.range.getColumn();

    if (e.source.getActiveSheet().getName() === ws) { const currentDate = new Date(); timestampCols.forEach(function (timestampCol) { if (col === timestampCol.col && row >= 5) { const cellValue = e.source.getActiveSheet().getRange(timestampCol.cell).getValue(); if (cellValue === "") { e.source.getActiveSheet().getRange(timestampCol.cell).setValue(currentDate); }} }); }}

    function addTimestamp5(e) { const timestampCols = [ { col: 3, cell: "C5" },{ col: 5, cell: "E5" },{ col: 7, cell: "G5" },{ col: 9, cell: "I5" },{ col: 11, cell: "K5" },{ col: 13, cell: "M5" },{ col: 15, cell: "O5" },{ col: 17, cell: "Q5" },{ col: 19, cell: "S5" },{ col: 21, cell: "U5" } ];

    const ws = "اخراج العيون - الملح"; const row = e.range.getRow(); const col = e.range.getColumn();

    if (e.source.getActiveSheet().getName() === ws) { const currentDate = new Date(); timestampCols.forEach(function (timestampCol) { if (col === timestampCol.col && row >= 5) { const cellValue = e.source.getActiveSheet().getRange(timestampCol.cell).getValue(); if (cellValue === "") { e.source.getActiveSheet().getRange(timestampCol.cell).setValue(currentDate); }} }); }}

    function addTimestamp5(e) { const timestampCols = [ { col: 3, cell: "C4" },{ col: 5, cell: "E4" },{ col: 7, cell: "G4" },{ col: 9, cell: "I4" },{ col: 11, cell: "K4" },{ col: 13, cell: "M4" },{ col: 15, cell: "O4" } ];

    const ws = "اللدود - الفم"; const row = e.range.getRow(); const col = e.range.getColumn();

    if (e.source.getActiveSheet().getName() === ws) { const currentDate = new Date(); timestampCols.forEach(function (timestampCol) { if (col === timestampCol.col && row >= 5) { const cellValue = e.source.getActiveSheet().getRange(timestampCol.cell).getValue(); if (cellValue === "") { e.source.getActiveSheet().getRange(timestampCol.cell).setValue(currentDate); }} }); }}

    function addTimestamp6(e) { const timestampCols = [ { col: 3, cell: "C4" },{ col: 5, cell: "E4" },{ col: 7, cell: "G4" },{ col: 9, cell: "I4" },{ col: 11, cell: "K4" },{ col: 13, cell: "M4" },{ col: 15, cell: "O4" } ];

    const ws = "اللبخة - الشعر"; const row = e.range.getRow(); const col = e.range.getColumn();

    if (e.source.getActiveSheet().getName() === ws) { const currentDate = new Date(); timestampCols.forEach(function (timestampCol) { if (col === timestampCol.col && row >= 5) { const cellValue = e.source.getActiveSheet().getRange(timestampCol.cell).getValue(); if (cellValue === "") { e.source.getActiveSheet().getRange(timestampCol.cell).setValue(currentDate); }} }); }}

    function addTimestamp7(e) { const timestampCols = [ { col: 2, cell: "B4" },{ col: 4, cell: "D4" },{ col: 6, cell: "F4" },{ col: 8, cell: "H4" },{ col: 10, cell: "J4" },{ col: 12, cell: "L4" },{ col: 14, cell: "N4" },{ col: 16, cell: "P4" },{ col: 18, cell: "R4" },{ col: 20, cell: "T4" } ];

    const ws = "أسحار الرحم"; const row = e.range.getRow(); const col = e.range.getColumn();

    if (e.source.getActiveSheet().getName() === ws) { const currentDate = new Date(); timestampCols.forEach(function (timestampCol) { if (col === timestampCol.col && row >= 5) { const cellValue = e.source.getActiveSheet().getRange(timestampCol.cell).getValue(); if (cellValue === "") { e.source.getActiveSheet().getRange(timestampCol.cell).setValue(currentDate); }} }); }}

    function addTimestamp8(e) { const timestampCols = [ { col: 3, cell: "C5" },{ col: 5, cell: "E5" },{ col: 7, cell: "G5" },{ col: 9, cell: "I5" },{ col: 11, cell: "K5" }, ];

    const ws = "جلسة الحجامة"; const row = e.range.getRow(); const col = e.range.getColumn();

    if (e.source.getActiveSheet().getName() === ws) { const currentDate = new Date(); timestampCols.forEach(function (timestampCol) { if (col === timestampCol.col && row >= 6) { const cellValue = e.source.getActiveSheet().getRange(timestampCol.cell).getValue(); if (cellValue === "") { e.source.getActiveSheet().getRange(timestampCol.cell).setValue(currentDate); }} }); }}

    function addTimestamp9(e) { const timestampCols = [ { col: 5, cell: "E20" },{ col: 6, cell: "F20" },{ col: 7, cell: "G20" },{ col: 8, cell: "H20" },{ col: 9, cell: "I20" },{ col: 10, cell: "J20" },{ col: 11, cell: "K20" },{ col: 12, cell: "L20" },{ col: 13, cell: "M20" },{ col: 14, cell: "N20" } ];

    const ws = "رش البيت"; const row = e.range.getRow(); const col = e.range.getColumn();

    if (e.source.getActiveSheet().getName() === ws) { const currentDate = new Date(); timestampCols.forEach(function (timestampCol) { if (col === timestampCol.col && row >= 22) { const cellValue = e.source.getActiveSheet().getRange(timestampCol.cell).getValue(); if (cellValue === "") { e.source.getActiveSheet().getRange(timestampCol.cell).setValue(currentDate); }} }); }}

  2. Assistant

    It seems like there are two functions with the same name addTimestamp5 which might cause undesired behavior. I've renamed the second one to addTimestamp5b and updated the onEdit() function accordingly.

    function onEdit(e) {
      addTimestamp1(e);
      addTimestamp2(e);
      addTimestamp3(e);
      addTimestamp4(e);
      addTimestamp5(e);
      addTimestamp5b(e);
      addTimestamp6(e);
      addTimestamp7(e);
      addTimestamp8(e);
      addTimestamp9(e);
    }
    
    // ...
    
    function addTimestamp5b(e) {
      const timestampCols = [
        { col: 3, cell: "C4" },
        { col: 5, cell: "E4" },
        { col: 7, cell: "G4" },
        { col: 9, cell: "I4" },
        { col: 11, cell: "K4" },
        { col: 13, cell: "M4" },
        { col: 15, cell: "O4" },
      ];
    
      const ws = "اللدود - الفم";
      const row = e.range.getRow();
      const col = e.range.getColumn();
    
      if (e.source.getActiveSheet().getName() === ws) {
        const currentDate = new Date();
        timestampCols.forEach(function (timestampCol) {
          if (col === timestampCol.col && row >= 5) {
            const cellValue = e.source
              .getActiveSheet()
              .getRange(timestampCol.cell)
              .getValue();
            if (cellValue === "") {
              e.source
                .getActiveSheet()
                .getRange(timestampCol.cell)
                .setValue(currentDate);
            }
          }
        });
      }
    }
    
    // ...
    

    With this change, each function has a unique name, and the code should work as intended.

  3. User

    MERGE THE THE TWO CODES FIRST CODE function onEdit(e) { addTimestamp2(e); }

    function addTimestamp2(e) { const timestampCols = [ { col: 3, cell: "C5" },{ col: 5, cell: "E5" },{ col: 7, cell: "G5" },{ col: 9, cell: "I5" },{ col: 11, cell: "K5"]; const ws = "2- العيون"; const row = e.range.getRow(); const col = e.range.getColumn(); if (e.source.getActiveSheet().getName() === ws) { const currentDate = new Date(); timestampCols.forEach(function (timestampCol) { if (col === timestampCol.col && row >= 6) { const cellValue = e.source.getActiveSheet().getRange(timestampCol.cell).getValue(); if (cellValue === "") { e.source.getActiveSheet().getRange(timestampCol.cell).setValue(currentDate); }} }); }}

    SECOND CODE =IF(C5="","",IF(Text(C5,"DDD")="Tue","الثلاثاء", IF(Text(C5,"DDD")="Wed","الأربعاء", IF(Text(C5,"DDD")="Thu","الخميس", IF(Text(C5,"DDD")="Fri","الجمعة", IF(Text(C5,"DDD")="Sat","السبت", IF(Text(C5,"DDD")="Sun","الأحد", IF(Text(C5,"DDD")="Mon","الأثنين")))))))) =IF(E5="","",IF(Text(E5,"DDD")="Tue","الثلاثاء", IF(Text(E5,"DDD")="Wed","الأربعاء", IF(Text(E5,"DDD")="Thu","الخميس", IF(Text(E5,"DDD")="Fri","الجمعة", IF(Text(E5,"DDD")="Sat","السبت", IF(Text(E5,"DDD")="Sun","الأحد", IF(Text(E5,"DDD")="Mon","الأثنين")))))))) =IF(G5="","",IF(Text(G5,"DDD")="Tue","الثلاثاء", IF(Text(G5,"DDD")="Wed","الأربعاء", IF(Text(G5,"DDD")="Thu","الخميس", IF(Text(G5,"DDD")="Fri","الجمعة", IF(Text(G5,"DDD")="Sat","السبت", IF(Text(G5,"DDD")="Sun","الأحد", IF(Text(G5,"DDD")="Mon","الأثنين")))))))) =IF(I5="","",IF(Text(I5,"DDD")="Tue","الثلاثاء", IF(Text(I5,"DDD")="Wed","الأربعاء", IF(Text(I5,"DDD")="Thu","الخميس", IF(Text(I5,"DDD")="Fri","الجمعة", IF(Text(I5,"DDD")="Sat","السبت", IF(Text(I5,"DDD")="Sun","الأحد", IF(Text(I5,"DDD")="Mon","الأثنين")))))))) =IF(K5="","",IF(Text(K5,"DDD")="Tue","الثلاثاء", IF(Text(K5,"DDD")="Wed","الأربعاء", IF(Text(K5,"DDD")="Thu","الخميس", IF(Text(K5,"DDD")="Fri","الجمعة", IF(Text(K5,"DDD")="Sat","السبت", IF(Text(K5,"DDD")="Sun","الأحد", IF(Text(K5,"DDD")="Mon","الأثنين")))))

  4. Assistant

    As the second code consists of Excel formulas, not JavaScript, you'll need to write JavaScript code equivalent to these Excel formulas and merge it with the first code. Below is the merged code using JavaScript:

    function onEdit(e) {
      addTimestamp2(e);
    }
    
    function addTimestamp2(e) {
      const timestampCols = [
        { col: 3, cell: "C5" },
        { col: 5, cell: "E5" },
        { col: 7, cell: "G5" },
        { col: 9, cell: "I5" },
        { col: 11, cell: "K5" },
      ];
      const ws = "2- العيون";
      const row = e.range.getRow();
      const col = e.range.getColumn();
      if (e.source.getActiveSheet().getName() === ws) {
        const currentDate = new Date();
        timestampCols.forEach(function (timestampCol) {
          if (col === timestampCol.col && row >= 6) {
            const cellValue = e.source.getActiveSheet().getRange(timestampCol.cell).getValue();
            if (cellValue === "") {
              e.source.getActiveSheet().getRange(timestampCol.cell).setValue(currentDate);
              const dayOfWeek = getDayOfWeek(currentDate);
              e.source.getActiveSheet().getRange(timestampCol.cell).setNote(dayOfWeek);
            }
          }
        });
      }
    }
    
    function getDayOfWeek(date) {
      const days = {
        Tue: "الثلاثاء",
        Wed: "الأربعاء",
        Thu: "الخميس",
        Fri: "الجمعة",
        Sat: "السبت",
        Sun: "الأحد",
        Mon: "الأثنين",
      };
    
      const dayName = Utilities.formatDate(date, "GMT", "EEE");
      return days[dayName] || "";
    }
    

    Here, I've added a new function getDayOfWeek to convert a JavaScript date into Arabic equivalent day names, and set the Arabic day of the week as a note in the cell, if the cell value is empty.

  5. User

    PLACE THE OUTPUT DAY IN ROW 4

  6. Assistant

    To place the output day in row 4, you'll need to adjust the row number in the setRange method when setting the dayOfWeek. Here's the updated code:

    function onEdit(e) {
      addTimestamp2(e);
    }
    
    function addTimestamp2(e) {
      const timestampCols = [
        { col: 3, cell: "C5" },
        { col: 5, cell: "E5" },
        { col: 7, cell: "G5" },
        { col: 9, cell: "I5" },
        { col: 11, cell: "K5" },
      ];
      const ws = "2- العيون";
      const row = e.range.getRow();
      const col = e.range.getColumn();
      if (e.source.getActiveSheet().getName() === ws) {
        const currentDate = new Date();
        timestampCols.forEach(function (timestampCol) {
          if (col === timestampCol.col && row >= 6) {
            const cellValue = e.source.getActiveSheet().getRange(timestampCol.cell).getValue();
            if (cellValue === "") {
              e.source.getActiveSheet().getRange(timestampCol.cell).setValue(currentDate);
              const dayOfWeek = getDayOfWeek(currentDate);
              e.source.getActiveSheet().getRange(timestampCol.cell.replace("5", "4")).setValue(dayOfWeek);
            }
          }
        });
      }
    }
    
    function getDayOfWeek(date) {
      const days = {
        Tue: "الثلاثاء",
        Wed: "الأربعاء",
        Thu: "الخميس",
        Fri: "الجمعة",
        Sat: "السبت",
        Sun: "الأحد",
        Mon: "الأثنين",
      };
    
      const dayName = Utilities.formatDate(date, "GMT", "EEE");
      return days[dayName] || "";
    }
    

    In the addTimestamp2 function, I've changed the line that sets the value for the day of the week to:

    e.source.getActiveSheet().getRange(timestampCol.cell.replace("5", "4")).setValue(dayOfWeek);
    

    This replaces the row number 5 with 4 in the cell's reference before setting its value with the day of the week.