Is there any difference in getting value in a variable using getValue and array value position (GAS)?

Question

Say I have an integer value "45,678.25" in cell A10 in google sheet.
Initially, I will use below command to assign the value in A10 into a variable:
Method1:
var myitem=sheet.getRange(10,1).getValue();

Then I noticed that I could also do the same with below command:
Method2:

var sheetdata=sheet.getDataRange().getValues();
var myitem=sheetdata[9][0];

I plan to change all from method 1 (getValue) to method 2 (calling by value position in DataRange) in all of my custom functions as I feel it looks tidier. But is it safe to do so? Will there be any impact on the end result/output?

I've created sampling test code with the output to show that there IS a significant impact to the output.

Sampling Code with Method 1 :

function checker(){
  var ss=SpreadsheetApp.openById('SHEET_ID').getSheetByName('mystaff'),
  staffdata=ss.getDataRange().getValues(),
  lastrow=ss.getLastRow(),
  staffID=ss.getRange(lastrow,1).getValue(),
  name=ss.getRange(lastrow,4).getValue(); // Marlina
  
    ss.getRange(lastrow,2).setFormula('=COUNTIF($D1:D'+lastrow+',D'+lastrow+')') // 2
    ss.getRange("mystaff!B"+lastrow+":B"+lastrow).copyTo(ss.getRange("mystaff!B"+lastrow), {contentsOnly: true});
    
//Method 1
    var RFQ=ss.getRange(lastrow,2).getValue();
    console.log("RFQ by getValue:"+RFQ); // 2
//end of Method 1
  
    if(staffID==""){
    if(RFQ>1){
       staffID=name.slice(0,3).toUpperCase();
      console.log("moreRFQ :"+RFQ);
      staffID=staffID+'('+RFQ+')';
      Logger.log("newstaffID : "+staffID);
    }else{
    staffID=name.slice(3,5).toUpperCase();
        console.log("firstRFQ :"+RFQ);
        staffID=staffID+'('+RFQ+')';
        Logger.log("newstaffID : "+staffID);
        }
    }
    ss.getRange(lastrow,1).setValue(staffID); // output : MAR(2)
}

Sampling Code with Method 2:

function checker(){
  var ss=SpreadsheetApp.openById('SHEET_ID').getSheetByName('mystaff'),
  staffdata=ss.getDataRange().getValues(),
  lastrow=ss.getLastRow(),
  staffID=ss.getRange(lastrow,1).getValue(),
  name=ss.getRange(lastrow,4).getValue(); // Marlina
  
    ss.getRange(lastrow,2).setFormula('=COUNTIF($D1:D'+lastrow+',D'+lastrow+')') // 2
    ss.getRange("mystaff!B"+lastrow+":B"+lastrow).copyTo(ss.getRange("mystaff!B"+lastrow), {contentsOnly: true});
   
//Method 2
    var RFQ=staffdata[lastrow-1][1];
    console.log("RFQ by array:"+RFQ); // blank
//end of Method 2
  
    if(staffID==""){
    if(RFQ>1){
       staffID=name.slice(0,3).toUpperCase();
      console.log("moreRFQ :"+RFQ);
      staffID=staffID+'('+RFQ+')';
      Logger.log("newstaffID : "+staffID);
    }else{
    staffID=name.slice(3,5).toUpperCase();
        console.log("firstRFQ :"+RFQ);
        staffID=staffID+'('+RFQ+')';
        Logger.log("newstaffID : "+staffID);
        }
    }
    ss.getRange(lastrow,1).setValue(staffID); // output : LI()
}

The different output is literally caused by the different subfunction it runs based on the IF..ELSE result, although the input source is the same. I'm so curious of why this could happen? Appreciate some logic explanation. Thanks.

if your only getting one value the code is generally simpler and I suspect faster but I'm no sure. You can test that for yourself. The key is to realize that if your going after a lot of values putting them in an array to process them is a lot faster. So positiioning them wisely in the sheet can really accelerate the performance of your script. But you can test this for yourself in any given situation. — Cooper, Oct 11 '20 at 17:22
Why don't you find the execution time between the two approaches for your specific test case to see which method suits you the best ? — Marios, Oct 11 '20 at 19:09
Well,faster process would be a bonus but I am more concern if it would impact the end result. I just noticed that in one of my custom functions, when I used method 2, when I put console.log down below the command, the value exists inconsistently in the variable when I test run multiple times and that led to inconsistent output. I wonder why but this doesn't happen when I used method 1. — dell, Oct 11 '20 at 19:25
Many people misunderstand customs functions. Please add a [mcve] to better understand what are you asking and your concerns. — Rubén, Oct 11 '20 at 19:30
Besides acceleration GAS has the limitation how many `getValue()` you can perform per second. So, if you process a big table with many cells it makes perfect sense to get them in one fell swoop. — Yuri Khristich, Oct 11 '20 at 23:37
Have a look at [this](https://stackoverflow.com/questions/63604878/is-setvalues-slower-than-setvalue) post. — ziganotschka, Oct 12 '20 at 09:27
I think that post just purely wanna have another alternative to setValue faster than what already has in place and wants to know the speed variance between the setValue and setValues @ziganotschka . I think that's not the answer I'm looking for with this question.thx anyway. — dell, Oct 12 '20 at 11:05

TheMaster · Accepted Answer · 2020-10-13T13:24:11.583

1

It is safe to do so and there's no impact on the end result or output.

Performance wise, when dealing with large data, getting all the data in one shot is the recommended best practice. However, if you only need specific ranges, take only the range that encompasses all the ranges you need and not all dataRange. If you need, A1, C1 and B5, You can take, getRange("A1:C5").getValues() and index into the resulting array accordingly.

Based on the updated code, There is another difference between the two checker functions: The confounding variable here is Time.

In method 1,

//Bunch of operations affecting the B column of range
var RFQ=ss.getRange(lastrow,2).getValue();
console.log("RFQ by getValue:"+RFQ); // 2

RFQ Value is retrieved after doing a bunch of other operations.

In method 2,

staffdata=ss.getDataRange().getValues(),
//Bunch of operations affecting Col B of last row
//Method 2
var RFQ=staffdata[lastrow-1][1];

staffdata values are retrieved before any changes are made to sheet.

To remove the confounding variable and to make both tests relatable, use getValues() in method2 at the same time/line in getValue() of method1.

//Bunch of operations affecting Col B of last row
//Method 2
const staffdata = ss.getDataRange().getValues();
const RFQ=staffdata[lastrow-1][1];

Alternatively, to make the tests unbiased,

var RFQ=ss.getRange(lastrow,2).getValue();// moved up
//Bunch of operations affecting the B column of range
console.log("RFQ by getValue:"+RFQ); // 2

edited Oct 13 '20 at 13:24

answered Oct 12 '20 at 16:31

TheMaster

45,448
6
62
85

After adding the https://stackoverflow.com/help/minimal-reproducible-example , I'm afraid I have to disagree with you, @TheMaster . – dell Oct 13 '20 at 12:46
Ok, seems like I have to declare the DataRange repeatedly each time I run an operation towards the previous value else the variable RFQ will not be updated. If that's the case Method 2 will only be adding more lines of codes rather than decreasing it. :( – dell Oct 13 '20 at 15:25
the getValue command will give me the latest value after the operation because it is being called after the range is updated. – dell Oct 13 '20 at 15:29
now I understood clearly why it made the difference and I think with this finding, I think it is safer to stick to Method 1. Thanks a lot for taking the time to clarify this to me, @TheMaster . Really appreciate it very much! – dell Oct 13 '20 at 15:31
@dell *I think it is safer to stick to Method 1.* Why? – TheMaster Oct 13 '20 at 19:03
@dell In both cases, if `getValue` or `getValues` are used at the same time, the result is the same. Read best practices. – TheMaster Oct 13 '20 at 19:11
I supposed I should add 'tidier' along with 'safer' in my comment on sticking to Method 1. – dell Oct 14 '20 at 00:40
Tidier because I don't have to keep on coding 2 lines (or a long code line) each time I wanna call the value after each operation (Method 2 needs me to declare DataRange first). Safer for especially someone who is often absentminded as myself. :D If I forgot to declare the DataRange first (even to just some or maybe just 1 subfunction) before calling back the value aft each operation, it would affect the output and troubleshooting to find the culprit is really time consuming. – dell Oct 14 '20 at 00:44
I guess we should emphasize the context getValues in here is focusing getDataRange.getValues and not getRange.getValues . – dell Oct 14 '20 at 00:48
1

@dell Method2 is cleaner and faster. If you want it 1 line, use `ss.getDataRange().getValues()[lastrow-1][1]`. Also, Don't mix formulas and scripts. In fact, Don't touch the spreadsheet after getting values. See https://stackoverflow.com/a/57836700/ Practice arrays. Don't do safer. Take risks as the rewards are worth the risk. – TheMaster Oct 14 '20 at 02:49
ouh, I just realized this `ss.getDataRange().getValues()[lastrow-1][1]` is one way to do the same in one line and definitely cleaner and faster. Thanks a lot, @TheMaster ! Now I can proceed changing in all my functions with confidence. Yeay! – dell Oct 14 '20 at 02:58
@dell Don't look at just changing the lines. Change your thought process entirely. – TheMaster Oct 14 '20 at 03:00
still learning as I'm entirely just 3months+ exploring this GAS which also means learning Javascript and HTML for email altogether. – dell Oct 14 '20 at 03:10
@dell I'm sure you are learning. That's why I'm driving you to learn it right. It's better than learning it wrong, erasing what you learnt previously and learning it right again. You'll do well, if you value what I said and read up and practice all the links. – TheMaster Oct 14 '20 at 03:17
Thanks, @TheMaster . Wise thought indeed. ;) – dell Oct 14 '20 at 07:27

Is there any difference in getting value in a variable using getValue and array value position (GAS)?

1 Answers1