Get selected values in row

Question

I want to be able to select a row and get the values in it. I have:

var id = ' ------gkqlDoUg'

 var sheet =    SpreadsheetApp.openById(id).getSheetByName('mysheeet')
 var vals= sheet.getActiveRange().getValues();

 Logger.log(vals)

when I run this there are no errors but I see:

[20-08-08 13:08:17:834 PDT] [[ ]]

How can I get this working?

Rubén · Accepted Answer · 2020-08-08T23:10:58.473

There is something wrong, maybe a bug, but this fails randomly. Anyway here is a super simple demo of about "how to get this working"

Create a spreadsheet
Add some values to a row
Select the row

The following script to a bounded project to the previous spreadsheet

function myFunction() {
  var values = SpreadsheetApp.getActiveRange().getValues();
  Logger.log(values);
}

Authorize the script
Run the function.

[20-08-08 15:58:57:727 CDT] [[adfa, afdfafs, dasfasf, , , , , , , , , , , , , , , , , , , , , , , ]]

I think that there is a bug related to use chaining with getActiveRange().

This sometime fails to get the expected result

function myFunction2(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var values = sheet.getActiveRange().getValues();
  Logger.log(values);
}

NOTE: The following snapshots were taken from the script's executions page.

Failed

Succeded

This gets the expected result

function myFunction3(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var range = sheet.getActiveRange();
  var values = range.getValues();
  Logger.log(values);
}

The following are questions that uses Class Sheet getActiveRange() chained with some Class Range methods like getRow(), getValues() but the current answers doesn't mention the cause of the problem, they just offer an alternative code

you showed screenshots of functions failing and suceeding above. where is that from? — user1592380, Aug 08 '20 at 22:57
@user1592380 Those screen shots come from the script execution page. On the script editor click on View > Executions. Also this page could be accessed navigating from https://script.google.com — Rubén, Aug 08 '20 at 23:09

Marios · Answer 2 · 2020-08-08T21:27:43.447

2

You can select a specific row (in this case 68) as follows:

var sheet= SpreadsheetApp.getActive().getSheetByName('mysheeet')
var target_row = 68;
var vals= sheet.getRange(target_row,1,1,sheet.getMaxColumns()).getValues().flat([1]);

The flat() method can be omitted. I am using it in case you want the array to be 1 dimensional.

If you want to use getActiveRange() use SpreadsheetApp.getActive() instead :

var sheet =  SpreadsheetApp.getActive().getSheetByName('mysheeet')
var vals= sheet.getActiveRange().getValues();

and make sure that you select the entire row like you do in the screenshot. In your example, I think you are not referencing to the correct spreadsheet file.

edited Aug 08 '20 at 21:27

answered Aug 08 '20 at 20:14

Marios

26,333
8
32
52

1

Have you tested the use of `getActiveRange().getValues()`? – Rubén Aug 08 '20 at 21:18
@Rubén I see you replicated my second approach in your answer. Why would you do that? – Marios Aug 08 '20 at 21:37
1

"Your second approach" actually super close to the OP approach. I doesn't work for the OP and doesn't work to me either. Does it work for you? – Rubén Aug 08 '20 at 21:43
It is not even close. There is a fundamental difference which is getting the active spreadsheet. There is a high chance that the OP made a mistake when referencing the spreadsheet id. So the getActive()/getActiveSpreadsheet() is a fundamental difference to OP's approach. Unfortunately, I will have to flag your answer. – Marios Aug 08 '20 at 21:47
@MariosKaramanis - I would take Rubén's side here, not sure where the second approach is replicated - in fact, it is indeed likely that the OP suffered from a bug, and it was referenced as such. Btw, do visit us in the [community chat room](https://chat.stackoverflow.com/rooms/217630/google-apps-script-chat-community), I see you are becoming active in the tag – Oleg Valter is with Ukraine Aug 09 '20 at 01:38

score 2 · Answer 3 · answered Aug 08 '20 at 20:48

2

Try:

Logger.log(vals[0].join(','));

answered Aug 08 '20 at 20:48

Cooper

59,616
6
23
54

Get selected values in row

3 Answers3

Linked