What are the steps to conduct a roll call using Google App Script?

Question

What are the steps to conduct a roll call using Google App Script?

After conducting some research on how to execute a row call using Google Apps Script, I have encountered a slight challenge and would greatly appreciate any assistance with this matter.

This script examines the values in the first column and uses them to rename new tabs.


function newSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var templateSheet = ss.getActiveSheet();
    var sheet1 = ss.getSheetByName("main");
    var getNames = sheet1.getRange("A2:A").getValues().filter(String).toString().split(",");

    for (var i = 0; i < getNames.length; i++) {
        var copy = ss.getSheetByName(getNames[i]);
        if (copy) {
            Logger.log("Sheet already exists");
        } else {
            templateSheet.copyTo(ss).setName(getNames[i]);
            ss.setActiveSheet(ss.getSheetByName(getNames[i]));
            ss.moveActiveSheet(ss.getNumSheets());
        }
    }
}

The image of the sheet can be found here:

https://i.sstatic.net/5CNCb.png

My current challenge lies in copying only the rows with specific data to the newly created tabs/sheets. For example, when creating a tab named Levi, I want only the row containing Levi's data to be copied to that sheet.

Currently, my code duplicates the entire source sheet onto the new tabs/sheets. Any help with refining this functionality will be highly appreciated.

javascript arrays google-apps-script google-sheets copy

Answer 1

Answer №1

Proposed solution:

To create a new sheet based on a main template, use the function .copyTo in order to copy all content from the main sheet. Retrieve the entire row corresponding to the specified name index.

Approach

Additional filtering is required to obtain the accurate row values for the new sheet. Filter the name column (column A) and iterate through the names to get their respective indices.

(Considering the possibility of gaps in the data, the for loop's index alone may not suffice).

After finding the correct index, increment it by one as Google Spreadsheets start row indexing at 1.

Use the function

.getRange(row, column, numRows, numColumns)

to easily retrieve the desired row. Determine the value for numColumns using the function .getLastColumn().

Subsequently, utilize .appendRow() to insert the row into the newly created sheet with .insertSheet().

Sample Code:

function newSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var templateSheet = ss.getActiveSheet();
    var sheet1 = ss.getSheetByName("main")
    var getNames = sheet1.getRange("A2:A").getValues().filter(String).toString().split(",");

    for (var i = 0; i < getNames.length; i++) {
        var copy = ss.getSheetByName(getNames[i]);
        if (copy) {
            Logger.log("Sheet already exists");
        } else {
            //The copyTo function will copy the entire sheet
            //templateSheet.copyTo(ss).setName(getNames[i]);

            var rowIndex = sheet1.getRange("A:A").getValues().flatMap(value => value[0]).indexOf(getNames[i]) + 1;
            var rowValues = sheet1.getRange(rowIndex, 1, 1, sheet1.getLastColumn()).getValues();
            ss.insertSheet(getNames[i]).appendRow(rowValues[0]);

            ss.setActiveSheet(ss.getSheetByName(getNames[i]));
            ss.moveActiveSheet(ss.getNumSheets());
        }
    }
}

Edit

If there are duplicated names, filter the column and extract the relevant indexes.

Create a set for the getNames variable to eliminate repetitions.

var getNames = [...new Set(sheet1.getRange("A2:A").getValues().filter(String).toString().split(","))];

Map the row indexes to the respective names in column A. Filter by the unique names to obtain the row indexes, then append these rows to the new sheet.

var rowIndexes = sheet1.getRange("A:A").getValues()
                                       .map((value, index) => [value[0], (index + 1)])
                                       .filter(value => value[0] === getNames[i]);
var namedSheet = ss.insertSheet(getNames[i]);
rowIndexes.map(index => {
                 var rowValues = sheet1.getRange(index[1], 1, 1, sheet1.getLastColumn()).getValues();
                 namedSheet.appendRow(rowValues[0]);
});

References:

Class Sheet

Answer 2

Proposed solution:

To create a new sheet based on a main template, use the function .copyTo in order to copy all content from the main sheet. Retrieve the entire row corresponding to the specified name index.

Approach

Additional filtering is required to obtain the accurate row values for the new sheet. Filter the name column (column A) and iterate through the names to get their respective indices.

(Considering the possibility of gaps in the data, the for loop's index alone may not suffice).

After finding the correct index, increment it by one as Google Spreadsheets start row indexing at 1.

Use the function

.getRange(row, column, numRows, numColumns)

to easily retrieve the desired row. Determine the value for numColumns using the function .getLastColumn().

Subsequently, utilize .appendRow() to insert the row into the newly created sheet with .insertSheet().

Sample Code:

function newSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var templateSheet = ss.getActiveSheet();
    var sheet1 = ss.getSheetByName("main")
    var getNames = sheet1.getRange("A2:A").getValues().filter(String).toString().split(",");

    for (var i = 0; i < getNames.length; i++) {
        var copy = ss.getSheetByName(getNames[i]);
        if (copy) {
            Logger.log("Sheet already exists");
        } else {
            //The copyTo function will copy the entire sheet
            //templateSheet.copyTo(ss).setName(getNames[i]);

            var rowIndex = sheet1.getRange("A:A").getValues().flatMap(value => value[0]).indexOf(getNames[i]) + 1;
            var rowValues = sheet1.getRange(rowIndex, 1, 1, sheet1.getLastColumn()).getValues();
            ss.insertSheet(getNames[i]).appendRow(rowValues[0]);

            ss.setActiveSheet(ss.getSheetByName(getNames[i]));
            ss.moveActiveSheet(ss.getNumSheets());
        }
    }
}

Edit

If there are duplicated names, filter the column and extract the relevant indexes.

Create a set for the getNames variable to eliminate repetitions.

var getNames = [...new Set(sheet1.getRange("A2:A").getValues().filter(String).toString().split(","))];

Map the row indexes to the respective names in column A. Filter by the unique names to obtain the row indexes, then append these rows to the new sheet.

var rowIndexes = sheet1.getRange("A:A").getValues()
                                       .map((value, index) => [value[0], (index + 1)])
                                       .filter(value => value[0] === getNames[i]);
var namedSheet = ss.insertSheet(getNames[i]);
rowIndexes.map(index => {
                 var rowValues = sheet1.getRange(index[1], 1, 1, sheet1.getLastColumn()).getValues();
                 namedSheet.appendRow(rowValues[0]);
});

References:

Class Sheet

What are the steps to conduct a roll call using Google App Script?

Answer №1

Proposed solution:

Approach

Sample Code:

Edit

References:

Similar questions

What is the process for displaying a list of all files within a folder?

Attempting to extract the class name of a tr tag but receiving a result of 'undefined'

Issue with local dependency not being loaded until after a hard refresh in Vue/Vue Router

Refreshing the current window with the ``Window.location.reload()`` function

When attempting to retrieve the data from a JSON file using an XMLHttpRequest, the result that is returned is [object object]

Guidelines for calculating the CRC of binary data using JQuery, javascript, and HTML5

Sending JSON Data with Javascript Post Request

jQuery plugin for uploading multiple files with validation for checking if they are empty or not

Tips for managing the return value of a PHP function in AJAX requests

CodeIgniter throwing an error with converting Array to String?

Improving User Experience with HTML Form Currency Field: Automatically Formatting Currency to 2 Decimal Places and Setting Maximum Length

Flawed Java Gzip decompression process on .NET platform

AngularJS: Trouble with directive recognizing attribute

What is the best way to automate a website that has identical buttons with the same class, but different HTML onlick functions?

The challenge of navigating through $scope

Having trouble with the pagination feature while filtering the list on the vue-paginate node

Creating a table header that displays the next and previous days using JavaScript

A React component will consistently display the most recent value of the props passed to

The function of modal in JavaScript is not working properly

What is the best way to omit a field from my query if the associated variable is empty?