Changing column order using map 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
Summary
This blog post will walk you through the process of changing columns in Apps Script. We will learn how to extract data from a specific sheet and rearrange the columns in a desired order.
Code
|
1 2 3 4 5 6 7 8 |
function colChange() { const SS = SpreadsheetApp.getActiveSpreadsheet(); const T_SHEET6 = SS.getSheetByName('Sheet6'); const data = T_SHEET6.getDataRange().getValues(); let dataMap = data.map(item => [item[1], item[0], item[2]]); Logger.log(dataMap); } |
Code Explanation
The code defines a function named colChange. Inside the function, it gets the active spreadsheet and assigns it to the SS variable. Then, it retrieves the sheet named ‘Sheet6’ and assigns it to the T_SHEET6 variable.
The getDataRange() method is used to get the range of data in the sheet, and the getValues() method is called to retrieve all the values in that range. The data is stored in the data variable.
The map() function is used to iterate through each item in the data array and create a new array dataMap with the columns rearranged. In this example, the second column is moved to the first position, the first column is moved to the second position, and the third column remains unchanged.
Finally, the dataMap array is logged to the Logger.
Example
Let’s say you have a spreadsheet with three columns: Name, Age, and Occupation. You want to rearrange the columns so that the Occupation column comes first, followed by the Name column, and then the Age column.
By executing the colChange function, the columns will be rearranged as desired.
AI Prompt
Create a function that changes the order of columns in a spreadsheet. The function should retrieve the data from a specific sheet and rearrange the columns in the desired order. Log the resulting data array to the Logger.
For example, if the original columns are [‘A’, ‘B’, ‘C’], and you want to rearrange them as [‘C’, ‘A’, ‘B’], the resulting array should be logged as [[‘C’, ‘A’, ‘B’]].
