Google Apps Scripts - How to add a two adjacent columns together then display the new value to the n-CodePudding

I want to be able to add all numbers in Column F - Male and Column G - Female to the Number of Passengers automatically using Google Apps Scripts.

CodePudding user response：

There are multiple approaches you can use.

Asuming you want to sum F1 G1 and display on H1 and so on.

You can set the value of the H column to be the corresponding SUM formula, or you can set the value to the result of the sum and do that in your code. The latter being slightly easier and more readable.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];


//the input would be the letter of the columns you want to aggregate
//start row dictates from which row you want to begin.
//adjacentColumn would be where the results are written. 
function aggregateColumns(column1, column2, startRow, adjacentColumn){
    let lastRow = sheet.getLastRow();
    for(let i = startRow; i < lastRow; i  ){
        var value1 = sheet.getRange(`${column1}:${i}`).getValue();
        var value2 = sheet.getRange(`${column2}:${i}`).getValue();
        sheet.getRange(`${adjacentColumn}:${i}`).setValue(value1 value2);
    } 
}

If you're unable to specify the adjacent column before hand, then you'd have to either translate A1 notation to row and column numbers, or simply use row and column numbers from the start.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];


//Column inputs should be the column number. Index starts at 1
//start row dictates from which row you want to begin.
function aggregateColumns(column1, column2, startRow){
    let lastRow = sheet.getLastRow();
    for(let i = startRow; i < lastRow; i  ){
        var value1 = sheet.getRange(i, column1).getValue();
        var value2 = sheet.getRange(i, column2).getValue();
        sheet.getRange(i, column2 1).setValue(value1 value2);
    } 
}

I recommend you to use template literals if you don't want to bother with row, column indexes and want to use A1 notation instead.

CodePudding user response：

Technically you don't need app scripts to automatically do have a dynamic column of ranges summed. You could put this formula in cell H2 and it will autopopulate... =Filter(F2:F G2:G,F2:F<>"")

If you wanted to create an app script to apply this to all sheets... you could do this:

/** @OnlyCurrentDoc*/

function runMacro() {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(addFormula_);
}

function addFormula_(_sh) {
  const formulaString = '=Filter(F2:F G2:G,F2:F<>"")'
  const theAddress = 'H2';
  _sh.getRange(theAddress).setFormula(formulaString);
}

CodePudding user response：

Those are great options. However, you can also test this method:

/** @OnlyCurrentDoc*/

function SumColumn() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = ss.getDataRange().getValues();

  var startRow = 1;
  
  for (var i=1; i <dataRange.length;   i){
    var row = dataRange [i];
    var column1 = row [5];
    var column2 = row [6];
    var sum = column1 column2 ;

    ss.getRange(startRow i,8).setValue(sum);

  }
  
}

The numbers in row [] belong to the column indexing where Column A is 0.

In your example, you are using Column F, which corresponds to the number [5], and Column G to the number [6].

However, the indexing of getRange() changes, and that one starts from 1. So Column A is 1, and for your case Column G is the number 8.

Reference

Indexing using getRange