To retrieve a range of values from a sheet in a Google Sheet using Apps Script, you can use the getValues() method of the Range class. Here’s an example code snippet:
function retrieveRangeValues() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = sheet.getRange('A1:C3'); // Replace 'A1:C3' with the desired range var values = range.getValues(); for (var i = 0; i < values.length; i++) { for (var j = 0; j < values[i].length; j++) { var value = values[i][j]; Logger.log('Value at row ' + (i + 1) + ', column ' + (j + 1) + ': ' + value); } } }
In this code, the retrieveRangeValues function first retrieves the active sheet using SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(). You can modify this line to access a specific sheet if needed.
The getRange() method is then used on the sheet object to define the range of cells you want to retrieve the values from. In this example, it’s set to 'A1:C3', but you can change it to any desired range, such as 'B5:E10' or 'D1:F'.
Next, the getValues() method is called on the range object to retrieve the values from the specified range. This returns a two-dimensional array where each element corresponds to a cell value.
A nested loop is used to iterate through the values array. The outer loop iterates through the rows, and the inner loop iterates through the columns. The value variable captures the value at each cell.
The values are logged using Logger.log() for demonstration purposes. Each log statement includes the row and column numbers along with the corresponding value.
You can call the retrieveRangeValues function from another function or set up a trigger to execute it based on your needs. When executed, it will retrieve the values from the specified range in the sheet and log them in the console.
Feel free to modify the code to suit your specific requirements and work with different ranges and operations on the values.