Google App Script is a scripting platform that allows you to write code to automate tasks in Google applications like Google Sheets, Google Docs, and Gmail. With Google App Script, you can create custom functions and scripts that can be used to extend the functionality of these applications. For example, you can use Google App Script to create a custom function that can be used in a Google Sheets spreadsheet to automatically generate reports or perform calculations.
It is based on JavaScript, so if you have experience with JavaScript, you should be able to quickly learn how to use Google App Script. In addition to JavaScript, Google App Script also provides a number of built-in libraries and classes that you can use to access and manipulate data in Google applications.
One of the key advantages of using Google App Script is that it allows you to integrate and automate tasks across different Google applications. For example, you can use Google App Script to automatically pull data from a Google Sheets spreadsheet and use it to create a new document in Google Docs. This can save you a lot of time and effort, as you can automate repetitive tasks and avoid having to manually transfer data between different applications.
Overall, Google App Script is a powerful and flexible tool that can help you automate tasks and extend the functionality of Google applications. Whether you’re a beginner or an experienced programmer, Google App Script offers a wide range of possibilities for creating custom solutions to meet your specific needs.
Setup for running app script code: Here are the steps you can follow to set up a Google Sheets API project and enable the API and authenticate with your Google account:
Step 1: Go to the Google Cloud Console App Script.
APP SCRIPT CONSOLE
Step 2: Select or create the project that you want to use for the Google Sheets API.
Project App Script
Step 3: Navigate to the API Library.
Step 4: Search for and select the Google Sheets API.
Select Sheets Api
Step 5: Enable the API.
APi selection
Step 6: Navigate to the Credentials page.
Step 7: Click the Create credentials button and select OAuth client ID.
Step 8: Choose the “Other” application type, and give the client ID a name (e.g., “Google Sheets API Client”).
Step 9: Click the Create button to generate the client ID and client secret, which you will use in your code to authenticate with the API.
Step 10: Install the necessary libraries and authenticate with your Google account in your code before using the API.
Problem Statement: How to make our code not stop even if we get an error on a row in the app script while parsing google-sheet.
Let’s assume we are parsing a google sheet with 1000 rows containing student data to find some useful insights on students but somehow a row in the sheet has invalid data which gives the error in run time. We are going to see how can we handle that.
Example: In this example of a Google App Script that can be used to scan 1000 entries from a Google Sheets spreadsheet containing student data:
You can run this script by going to the Google Sheets menu and selecting “Tools > Script editor”. Then, you can paste the code into the script editor and save it. Finally, you can run the scanStudentData() function by clicking the “play” button in the script editor.
Javascript
// Async function to retrieve data from // a Google Sheets spreadsheet and // log it to the console async function getSheetData() { // Specify the spreadsheet ID // and range of cells to access const spreadsheetId = '1O6KRpqiwBDXd1nxLdO5TagfppoAfJo3fWqxe7J4VH9Y'; const rangeName = 'Sheet1!A:E'; try { // Get the values from the spreadsheet // using the specified ID and range // The values property of the response // object contains an array of rows // from the specified range const values = Sheets.Spreadsheets.Values .get(spreadsheetId, rangeName).values; // If no values are found, log a message and return if (!values) { console.log('No data found.'); return; } // Iterate over each row in the values array for (const row in values) { // Try to log the row, and // log any errors that occur try { console.log(values[row]); } catch (err) { console.log(err); } } } catch (err) { // If there is an error getting // the values from the spreadsheet, // log the error message console.log(err.message); } } // Async function to call // the getSheetData function async function main() { // Call the getSheetData function await getSheetData(); } |
This code installs the required libraries, sets the ID of the Google Sheets that you want to access, sets the range of cells that you want to retrieve from the worksheet, sets up the Google Sheets API client, sets the credentials for the client, uses the Sheets API to retrieve the data from the specified range, loops through the rows in the worksheet, and does something with the student data in each row (in this example, it simply prints the student data to the console).
Explanation:
- This code retrieves data from a Google Sheets spreadsheet using the Sheets API.
- The ID of the target spreadsheet and the range of cells to access are specified as constants.
- The getSheetData function is defined as async, allowing it to use the await keyword to pause execution until a response is received from the API.
- The getSheetData function makes a request to the Sheets API to retrieve the specified range of cells from the spreadsheet.
- If the API returns a successful response, the values property of the response object is stored in the values variable.
- If no values are found in the response, a message is logged to the console and the function returns.
- If values are found, a message indicating the data being printed is logged to the console.
- The for loop iterates over each row in the values array.
- The try block attempts to log the current row to the console.
- If an error occurs while logging the row, the catch block logs the error to the console.
- The try-and-catch blocks within the for loop allow the function to continue executing even if an error occurs while processing a particular row.
- If an error occurs while making the API request, the catch block outside of the for loop logs the error message to the console.
- The main function is defined as async and calls the getSheetData function using the await keyword.
- The main function serves as a wrapper for the getSheetData function, allowing it to be called elsewhere in the code.
The getSheetData function logs the name and major of each student in the spreadsheet to the console. - The range of cells being accessed is specified as ‘Sheet1!A:E’, which means all cells from A to E on the sheet named “Sheet1” is being accessed.
- The for loop iterates over each row in the values array and logs the values in the first and fifth columns (indices 0 and 4).
- The try-and-catch blocks within the for loop allow the function to continue executing even if an error occurs while processing a particular row.
- If an error occurs while making the API request, the catch block outside of the for loop logs the error message to the console.
- This code allows for the retrieval and processing of data from a Google Sheets spreadsheet using the Sheets API.
Output:
This output is generated by the console.log statement in the code, which prints the student data for each row in the worksheet to the console. The student data is assumed to be stored in the first four columns of the worksheet (i.e., columns A through D), with the first column containing the student’s first name, the second column containing the student’s last name, the third column containing the student’s address, and the fourth column containing the student’s city.
