Google App Script - How to change Value of cell from a Range?-CodePudding

I'm self learner in app script and please don't judge me hard. I'm working on a project where I want to change cell value from a specific range, that are codes, to a numbers.

Here's what I have tried to write

function ShiftChange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ws = ss.getSheetByName("Roster");
  var range = ws.getRange("G5:NH17");
  var data = range.getValues();
  
  for(var i=1;i<ws.getLastRow();i  ){
    for(var y=1;y<ws.getLastColumn();y  )
      var value = ws.getRange(i, y).getValue();
    if(value == 'E')
    var cell = ws.getCell(i,y);
    cell.setValue("08 - 17");
  }
}

I have this part

and I want this result

Here is the spreadsheet

https://docs.google.com/spreadsheets/d/1i3qMZgls3_WRPY5QSlQJo802D7xYDO80dih8E2-Vrd4/edit?usp=sharing

Can somebody help me with that?

CodePudding user response：

Then i think this sample sheet is what you are after. There is a custom function inside Extension -> Apps script. You can use this function like a normal formula:

=MAPPING(A2:C10)

The script:

/**
* Returns mapping of roster
*
* @param {A1:A5} range Input range.
* @return {array} codes converted to numbers.
* @customfunction
*/
function MAPPING(input) {
  const mapping = {
    'E': '08 - 17',
    'D': '09 - 18',
    'M': '11 - 20',
    'L': '15 - 20',
    'N': '17 - 02',
    'O': 'Off'
  }
  const results = []

  input.forEach(row => {
    const tempArray = [];
    row.forEach(col => {
      const char = col.substring(0, 1)
      const number = mapping[char]
      tempArray.push(number)
    })
    results.push(tempArray)
  })

  return results
}