Extracting Columns from Spreadsheet using map method in Apps Script

This post is a corrected post after AI explains the code included in the book “Google Apps Script 101: Building Work Automation For Free”. Added a strikethrough when editing what AI has written, and added color when edited by author

Table of Contents

1. Summary
2. Code
3. Code Explanation
4. Example
5. AI Prompt

Summary

In this blog post, we will explore how to extract specific columns from a spreadsheet using Apps Script. We will use the getDataRange method to get all the data from a sheet, and then manipulate the data to extract the desired columns.

Code

Code Explanation

The code defines a function named colExtract. It retrieves the active spreadsheet using getActiveSpreadsheet method and assigns it to the SS variable. Then it gets the sheet named ‘Sheet6’ using getSheetByName method and assigns it to the T_SHEET6 variable.

The getDataRange method is used to get the range of data from the sheet, and getValues is used to retrieve the values of that range. The data is stored in the data variable.

The map method is used on the data array to create a new array, dataMap, containing only the first and third elements of each inner array. This is done by accessing the elements using square bracket notation.

Finally, the dataMap array is logged using Logger.log.

Example

Let’s say we have a spreadsheet with three columns: Name, Age, and City. We want to extract only the Name and City columns.

After running the colExtract function, the dataMap array will contain an array of arrays, where each inner array contains the Name and City values of each row.

AI Prompt

Write a function that extracts specific columns from a spreadsheet. The function should retrieve the active spreadsheet, get the sheet by name, and extract the desired columns using the getDataRange method and map function. Log the extracted columns.