I'm trying to create an apps script to autofill a gantt chart when the sheet is edited, but having trouble.

Here is a link to the spreadsheet if it helps.

function ganttChart()
{
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ganttSheet = ss.getSheetByName("Gantt Chart");
  var headerRow = ss.ganttSheet.getRange('headerRow').getRow();
  var lastRow = ss.ganttSheet.getLastRow();
  var lastCol = ss.ganttSheet.getLastColumn();
  var firstTask = headerRow   1
  var taskRoleCol = ss.ganttSheet.getRange('taskRole').getColumn();
  //I'm not sure if I need to do the below RoleCol if I already have a named range -- this will return an integer which is the column #
  var roleCol = ss.getSheetByName("Roles").getRange('Roles').getColumn();
  var taskCol = ss.ganttSheet.getRange('taskNames').getColumn();
  var startWeekRow = ss.ganttSheet.getRange('startWeek').getRow();
  var expDurationCol = ss.ganttSheet.getRange('expDuration').getColumn();

  //set the requirements for the edit trigger -- not sure what these would be
  //if (e.range)
  //{
    for (var i = firstTask; i < lastRow; i  )
    {
      var currentTask = ss.ganttSheet.getRange(i, taskCol).getValue();
      var currentStartWeek = ss.ganttSheet.getRange(i, startWeekCol).getValue();
      var currentTaskExpDuration = ss.ganttSheet.getRange(i,expDurationCol).getValue();
      var currentTaskRole = ss.ganttSheet.getRange(i,taskRoleCol).getValue();

      if (currentTask != null)
      {
        if (currentStartWeek != null)
        {
          //for loop to identify the column that matches the start week #
          for (var j = 0; j < lastCol; j  )
          {
            
            var checkWeek = ss.ganttSheet.getRange(startWeekRow, j).getValue();

            if (checkWeek == currentStartWeek)
            {
              
              //identify the range
              var taskTimeRange = ss.ganttSheet.getRange(i,j - 1,(currentTaskExpDuration*2  1), 1);
              //for loop get the background color based on role
              for (var k = 0; k < lastRow; k  )
              {
                var checkRole = ss.ganttSheet.getRange(k, roleCol).getValue();
                //if role value matches the currentTaskRole
                if (checkRole == currentTaskRole)
                {
                  
                  var roleColor = ss.ganttSheet.getRange(k, roleCol).getBackground();
                  //reformat the range based on duration
                  taskTimeRange.setBackground(roleColor);

                }
                  
              }
              
            }
          }
        }
      }
    }
  //}
}

I took off the "onEdit" to try and get the program to work on run, but I'm still getting a "cannot read properties of undefined" error.

What should happen is:

• when a user edits the "Gantt Chart" sheet
• the program changes the background color in the corresponding range to indicate the weeks a task is being worked on, based on the start week and calculated duration
• the background color should correspond to the task role, based on the colors set in the "Roles" sheet
• If the above isn't clear, here is a link to a video where I try to explain what the program should do

CodePudding user response：

The question relates to onEdit but the trigger is, at this point of development of the script, irrelevant, since the script is littered with syntax errors.

Even so, I suggest that the trigger is irrelevant in any event. The script can/should be triggered when the data has been populated. This trigger can be done manually, or (perhaps) via a menu option.

getRange(), getLastRow() and getLastColumn are sheet-based methods.

Incorrect

var headerRow = ss.ganttSheet.getRange('headerRow').getRow()
var lastRow = ss.ganttSheet.getLastRow()
var lastCol = ss.ganttSheet.getLastColumn()

Correct

var headerRow = ganttSheet.getRange('headerRow').getRow()
var lastRow = ganttSheet.getLastRow()
var lastCol = ganttSheet.getLastColumn() 

Other
startWeekCol is not defined
for (var j = 0; j < lastCol; j )

• "j" substitutes for the column number, but a value of 0 is invalid

var taskTimeRange = ganttSheet.getRange(i,j - 1,(currentTaskExpDuration*2 1), 1)

• when "j" is 1, "j-1" resolves to 0 (zero) which is invalid

for (var k = 0; k < lastRow; k )

• "k" substitutes for the row number, but a value of 0 is invalid