Typescript to change cell value based on value within another cell

Hi eikstein ,

Please, check if the code below works for you. I tried it with this fake tables:

The tables have the names "Tasks" and "Teams" (it is relevant for your Typescript code).

The code is the following:

function main(workbook: ExcelScript.Workbook) {
  const tasksTableName = "Tasks" //replace for your tasks table name
  const teamsTableName = "Teams" //replace for your teams  table name

  const tasksTable = workbook.getTable(tasksTableName)//.getRangeBetweenHeaderAndTotal()

  const teamsTable = workbook.getTable(teamsTableName)
  
  for (let i = 0; i < tasksTable.getRangeBetweenHeaderAndTotal().getValues().length; i++){
    
    if(tasksTable.getColumnByName('Status').getRangeBetweenHeaderAndTotal().getRow(i).getValue() === "closed"){
      const currentOwner = tasksTable.getColumnByName('Owner').getRangeBetweenHeaderAndTotal().getRow(i).getValue()
      let teamName:String = null

      teamsTable.getColumns().map(col => {
        const usersInTeam = Array.from(col.getRangeBetweenHeaderAndTotal().getValues())
        if (usersInTeam.flat().includes(currentOwner)){
          teamName = col.getName()
        }
      })

      if (teamName !== null){
        tasksTable.getColumnByName('Owner').getRangeBetweenHeaderAndTotal().getRow(i).setValue(teamName)
      }
      
    }
    
  }
}

Note that:

a. At rows 2 and 3, you may change the strings to your table names ("Tasks" and "Teams", in the example).

b. The code catches the data from the "Tasks" table based in the columns name ("Owner" and "Status", in this case). So make sure to change it if your real table has different column names.

c. The algorithm is case sensitive, so it will ignore "Closed" status (accept only "closed"). The same thing about the owners name: if you have "user 1" in your Tasks table and "User 1" in your Teams table, the owner won't be caught. If it is an issue for you, we can work to solve this problem by adding some text cleaning on the Typescript code.

Let me know if it solves your problem.