Differences between getRange, getDataRange, and getActiveRange?

Question

If it isn't immediately apparent by the question, I am pretty new to Google Apps Script. I'm trying to write a Spreadsheets function that runs through each row of a form submission sheet in a workbook and update a second sheet/range (in the same workbook) based on the values of the cells in the first using a for loop. However, nothing happens when I run the function.

I'm pretty sure the error is in how I'm defining the ranges in question, but I'm not 100% sure. Here's a modified version of what I've written:

function update() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var secondSheet = ss.getSheets()[1];
  var submissionSheet = ss.getSheets()[0];

  var secondRowEnd = secondSheet.getLastRow();
  var submissionRowEnd = submissionSheet.getLastRow();

  var secondColumnEnd = secondSheet.getLastColumn();
  var submissionColumnEnd = submissionSheet.getLastColumn(); //used to define ranges dynamically

  var secondRange = secondSheet.getValues();
  var submissionRange = submissionSheet.getValues();

  for(var i = 2; i <= submissionRowEnd; i++) {

    var rock = secondRange.getCell(i, 1).getValue();
    var paper = secondRange.getCell(i, 2).getValue();
    var scissors = secondRange.getCell(i, 3).getValue();

    var status = secondRange.getCell(i, 4).getValue();
    var forStatus = secondRange.getCell(i, 5).getValue();

    if (status === "Do X!") {
      for(var j = 2; j <= submissionRowEnd; j++) {

        var jrock = submissionRange.getCell(j, 1).getValue();
        var jpaper = submissionRange.getCell(j, 2).getValue();
        var jscissors = submissionRange.getCell(j, 3).getValue();
        var jstatus = submissionRange.getCell(j, 4).getValue();

        if (status === forStatus) {
          jrock.setValue(rock);
          jpaper.setValue(paper);
          jscissors.setValue(scissors);
        } else { /*do nothing*/ }
      }
    }
  }

I've been staring at slightly different versions of this code for weeks now, so any and all eyes on this would be greatly, greatly appreciated!

Javascript != Java and from your code it seems you're using Javascript, so please remove the Java tag. — Thomas, Jul 19 '16 at 14:18
Answered my own question: isolating individual cells by using getRange() instead of getCell().getValue() seems to have done the trick. I'm too inexperienced to know whether or not that's a "pretty" solution, but it works! — Maru, Jul 19 '16 at 18:37

score 10 · Answer 1 · answered Jul 19 '16 at 14:55

Just checkout out the Apps Script API reference for the Spreadsheet Service. All the functions are documented there. Here are links to descriptions of the methods you need:

getRange() has multiple implementations.

score 3 · Answer 2 · edited Mar 19 '20 at 11:18

3

the usage of Sheet.getDataRange() yields to a fast response time compared to Sheet.getRange(), since Sheet.getDataRange() returns a data array

Hope This Helps (HTH)

edited Mar 19 '20 at 11:18

SHR

7,940
9
38
57

answered Mar 19 '20 at 09:23

Trajano Roberto

179
2
7

score 1 · Answer 3 · edited Aug 05 '20 at 22:52

Your spreadsheet in gdrive communicates with your function in the google apps script for every getRange().getValue(). That is, the number of cells that are data, maybe a hundred times or more.

function update() {var ss = SpreadsheetApp.getActiveSpreadsheet();
var secondSheet=ss.getSheets()[1];var submissionSheet = ss.getSheets()[0];
var secondRowEnd=secondSheet.getLastRow();
var data = submissionSheet.getDataRange();//here is all 
datasecondSheet.getRange(secondRowEnd).setValue(data.map(function (x){return 
x=data.filter( function (){blabla})}))}

Differences between getRange, getDataRange, and getActiveRange?

3 Answers3