Spreadsheet Service

App script

This tutorial will help in fetching data from google sheets using the app script.

Google spreadsheet with sheet name “tutorial”

Using the above sheet, will try to fetch the information in the sheet using app script. Example use case — Get notified when someone submits a google form.

Breakdown:

1. SpreadsheetApp service is used here.
2. openById will open the sheet (id can be taken from the URL of the sheet).

getSheetByName('sheet_name')

This will select the sheet with the given name. Here ‘tutorial’.

4. Data Range function returns a Range corresponding to the dimensions in which data is present.

5. getValues — returns the rectangular grid of values for this range.

Code:

function services() {
var ss = SpreadsheetApp.openById(
 ‘1XXtDLGCMpS-YBz35lGhalNkNxW_blZPI_JsPE5SGCvo’ 
 ); // change the vaue to the sheet ID
 
var sheet = ss.getSheetByName(‘tutorial’); // default sheet name is ‘Sheet1’
var range = sheet.getDataRange();
var data = range.getValues();
Logger.log(JSON.stringify(data));
}

Click on View-> log

Log containing the fetched data from our ‘tutorial’ sheet

Note: This script requires authorisation, which can be given when the script is run for the first time.