Looping through rows, get values, and add to total on another sheet

Question

So I'm stumped on this in Google Sheets.

Sheet 'Price Calculator' Qty has a of items bought and sold in Column A, separated into 2 named ranges TRADE_QTY and BUY_QTY.

An identical List appears in sheet 'Master Tally', with qtys from previous trades, also in column A.

Have been flipping through multiple windows of examples of code and none seem to be able to provide anything that works.

function TEST() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();

 //Gets number of rows of each range
 var Rows1 = ss.getRange('\'PRICE CALCULATOR\'!TRADE_QTY').getNumRows()
 var Rows2 = ss.getRange('\'PRICE CALCULATOR\'!BUY_QTY').getNumRows()
 //Gets Starting rows of each range
 var Row1 = ss.getRange('\'PRICE CALCULATOR\'!TRADE_QTY').getRow()
 var Row2 = ss.getRange('\'PRICE CALCULATOR\'!BUY_QTY').getRow()

    for (r=Row1; r<Rows1; r++) {
    ss.setActiveSheet(ss.getSheetByName('PRICE CALCULATOR'), true);
    var ADD = ss.getRange(r,1).getValue()
    if (cell.isBlank()) {
      next r
    }
    else {
    ss.setActiveSheet(ss.getSheetByName('Master Tally'), true);
    var EXIST = ss.getRange(r,1).getValue()
    var TOT = ADD+EXIST
    ss.getRange(r,1).setValue(TOT)
    }
  }
}

Basically i'm try to develop a macro/script that adds the new trade qtys in sheet 'Price Calculator' to the existing qtys in 'Master Tally'

I"m stumped as it keeps throwing me 'Cannot find method getRange(number,number)' and now i'm out of my depth!

Link to the document; https://docs.google.com/spreadsheets/d/1gIjCqv5KT41wYuJS1Hs1X8yPPUTPY_kGoTuilzxLkSo/edit?usp=sharing

it keeps throwing me getRange errors Please include the specific error (including the line number. Also it is difficult to imagine how one column can include two lists (both "bought" and "Sold"), so please share your spreadsheet or a sample version thereof). It wouldn't hurt to read How to create a Minimal, Complete, and Verifiable example. — Tedinoz
Thanks for sharing the spreadsheet. But you shared View only. This means that anyone trying to help can't make a copy of the sheet, or even copy any of the data from the sheets. It makes it quite a bit harder because one must then create their own spreadsheet, sheets, data layout and data. It's actually a disincentive to get involved with developing an answer. — Tedinoz

Tedinoz Tedinoz · Accepted Answer · 2019-05-10T04:55:40

This code suffers from a basic flaw: confusion between Row and Column numbers of an array (which start at 0-zero) with those derived from script commands such as getValue (which start at 1-one).

For example:

for (r=Row1; r<Rows1; r++) {
In this case, the value of Row1 was determined by getRow, so it returns the actual row number. But the loop values will generate the row and column number for an array starting at zero; so this line should read for (r=0; r<Rows1; r++) {
var EXIST = ss.getRange(r,1).getValue()
The purpose of this line is return the "existing qtys in 'Master Tally'", and the range will look in Column A for the value. However the values are actually in column B. So this line will never return an accurate value for "existing qtys".

There are some other things to note:

The existing code makes two getValue calls in every loop; these are time-expensive. The answer improves performance by getting the respective range values just once before the loop.
The update of the quantity sold (setValue(TOT)) is inside the loop. Again this is a time-expensive command. The answer updates the array values within the loop, and then updates the sheet once-only after the loop.
BUY Qty values are irrelevant

function so56017521() {

  var ss = SpreadsheetApp.getActive();

  //Gets number of rows of each range
  var Rows1 = ss.getRange('\'PRICE CALCULATOR\'!TRADE_QTY').getNumRows()
  //Logger.log("DEBUG: Number of Rows: Trade Qty="+Rows1);

  //Gets Starting rows of each range
  var Row1 = ss.getRange('\'PRICE CALCULATOR\'!TRADE_QTY').getRow()
  //Logger.log("DEBUG: Start Row: Trade Qty="+Row1);

  // setup sheets
  var calcsheet = "PRICE CALCULATOR";
  var mastersheet = "Master Tally";
  var calc = ss.getSheetByName(calcsheet);
  var master = ss.getSheetByName(mastersheet);
  var masterrows = master.getLastRow();
  //Logger.log("DEBUG: Master Last Row = "+masterrows);

  // get data for each sheet
  var calcrange = calc.getRange(Row1, 1, Rows1);
  var calcdata = calcrange.getValues();
  var masterrange = master.getRange(3, 2, masterrows - 2);
  var masterdata = masterrange.getValues();
  //Logger.log("DEBUG: Calc data range = "+calcrange.getA1Notation()+", Master Data Range"+masterrange.getA1Notation());

  for (r = 0; r < Rows1; r++) {
    Logger.log("r=" + r);
    var ADD = calcdata[r][0]; //Trade qty
    //Logger.log("DEBUG: r="+r+", ADD value = "+ADD+", ADD.length = "+ADD.toString().length);

    if (ADD.toString().length != 0) { // if Trade qty has value
      // keep going
      //Logger.log("DEBUG: keep going");
      var EXIST = masterdata[r][0]; // existing quantity qty sold
      Logger.log("DEBUG: r=" + r + ", EXIST = " + EXIST);
      var TOT = ADD + EXIST; // sum of trade-in qty plus existing qty
      Logger.log("DEBUG: ADD+EXIST = " + TOT);

      // update masterdata array
      masterdata[r][0] = TOT;
    } else {
      // nothing to see here
      //Logger.log("DEBUG: next r please");
    }
  }
  //update the spreadsheet with the adjusted array values
  masterrange.setValues(masterdata);
}

Looping through rows, get values, and add to total on another sheet

1 Answers