Read values from spreadsheet

Question

Read values from spreadsheet

1.6k Views Asked by Nahush Sarje At 04 April 2025 at 16:37

I am trying to read values from google spreadsheets.

    String range = "A1:20";
    ValueRange response = sheet.spreadsheets().values().get(spreadsheetId, range)
            .execute();

    List<List<Object>> values = response.getValues();
    if (values == null || values.size() == 0) {
        System.out.println("No data found.");
    } else {
        System.out.println("Name, Major");
        System.out.println(values);
        for (List row : values) {
            // Print columns A and E, which correspond to indices 0 and 4.
            System.out.printf("%s\n", row.get(0));
        }
    }

I am having a spreadsheet with multiple rows and columns. I need to read the entire data present in the sheet. Above code is working fine and reading 20 rows of the first column but I am not able to set range variable values to automatically read all rows and column from sheet.

In addition I want to read data types of every column with values in json format at server end.

Original Q&A

There are 3 best solutions below

**utphx** · Answer 1

If I am understanding you correctly, you want to get data in the entire sheet using getDataRange() found here:

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()

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

 // This represents ALL the data
 var range = sheet.getDataRange();

 // This get the values
 var values = range.getValues();

**Jason Allshorn** · Answer 2

If you need to call to the sheet from the client then I have this code that I often use. Note that the sheet needs to be published, and set to anyone with the link can view. You will also need jquery. Notice how the sheet URL out puts to json. The column headers get a prefix of "gsx$" and then set to lowercase with white spaces removed.

//https://docs.google.com/spreadsheets/d/1OY18xmnT-O4AWzGDC63RvzzmNgyKHOnuvq-RX6M_6xw/edit#gid=0

var spreadsheetID = "1OY18xmnT-O4AWzGDC63RvzzmNgyKHOnuvq-RX6M_6xw";
var url = "https://spreadsheets.google.com/feeds/list/" + spreadsheetID + "/1/public/values?alt=json";
$.getJSON(url, function(incomingData) {
 data = incomingData;
    var entry = data.feed.entry;
    showData(entry)
});

function showData(entry){
  for (var items in entry){
  console.log(entry[items].gsx$fruit.$t +": "+ entry[items].gsx$number.$t )
}
}

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <title>JS Bin</title>
  <script src="https://code.jquery.com/jquery-3.1.0.js"></script>
  
</head>
<body>

</body>
</html>

**Karl_S** · Answer 3

See the full code at the Simple Mail Merge tutorial for a good way to read the data. Especially the part from the getRowsData() function down. It will read all the data in a row.

For your range, the first 3 lines of the above mentioned code gets you what you need. I modified them slightly below to get a range representing all of Sheet1:

var ss = SpreadsheetApp.openById(spreadsheetId);
var dataSheet = ss.getSheetByName('Sheet1');
var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 4);

Read values from spreadsheet

There are 3 best solutions below

Related Questions in GOOGLE-SHEETS

Related Questions in GOOGLE-SHEETS-API

Related Questions in GOOGLE-API-JAVA-CLIENT

Trending Questions

Popular # Hahtags

Popular Questions