Search code examples
loopsfor-loopgoogle-apps-scriptgoogle-sheets

Google Sheets Macro - Go through a column and changes numerical values based on the original value

So I don't have much code experience but I'm trying to go through a column a change its values based on this rule:

if 1 CHANGES to 5

if 2 CHANGES to 4

if 4 CHANGES to 2

if 5 CHANGES to 1

The IF party is easy and its working, but its only working on a single cell.

I need it to go through the whole column. Also, since number of rows of said column will vary from sheet to sheet, I'm trying to get the length of the column instead of a fixed position. It will always start on the same cell though.

function inverter() {
  var spreadsheet = SpreadsheetApp.getActive();
  var lastRow = spreadsheet.getLastRow();
  var area = spreadsheet.getRange('K2');
  var sheet = spreadsheet.getActiveSheet();
  var values = area.getValues();
  var tamanho = sheet.getRange(2, 11, lastRow); 
  for (var i=0; i<tamanho.length; i++){
    if (spreadsheet.getCurrentCell().getValue() == 1) {
      spreadsheet.getCurrentCell().setValue(5) 
    }
    else if (spreadsheet.getCurrentCell().getValue() == 2) {
      spreadsheet.getCurrentCell().setValue(4) 
    } 
    else if (spreadsheet.getCurrentCell().getValue() == 4) {
      spreadsheet.getCurrentCell().setValue(2) 
    }
    else if (spreadsheet.getCurrentCell().getValue() == 5) {
      spreadsheet.getCurrentCell().setValue(1) 
    }
  }
}

Thats what I've tried so far but its not working. It seems that its not entering the loop properly but I can't figure out what I'm missing.

Solution

  • Your problem is getCurrentCell() never changes. Here is an example of how to do it but a few tips.

    1. Use getValues() and setValues() as much as possible, getValue()/seetValue() can seriously degrade performance.
    2. Assuming all values in column K are from 1 to 5, you can simple subtract 6 to get the inverse.
    3. Array methods are more efficient than for loop so use Array.map() or Array.forEach()
    4. Many programs are now using the Arrow function => for easier reading.

    Code.gs

    function inverter() {
  let spread = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spread.getActiveSheet();
  let values = sheet.getRange(2,1,sheet.getLastRow()-1,1).getValues();
  values = values.map( row => [Math.abs(6-row[0])] );
  sheet.getRange(2,2,values.length,1).setValues(values);
}

    Reference