CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Jensen010
Jensen010

Reputation: 445

Google apps script - Importing sheet values from another sheet

I'm pulling my hair out a little trying to write a conditional Google apps script for Google sheets. The sheet has 5 tabs. Basically, the sheet gets it's data from a google form. Clients fill the form out and it populates our "Master" tab of the sheet in question.

Here's where I'm hung up: The form is used for teachers to refer students to specific administrators. Whoever is controlling the form needs to be able to assign a specific row of data to a specific administrator, which each have their own tab set up. They do this by selecting, from a drop down, which admin they want to assign the row to.

So, if row 2 is assigned to "Miranda," Certain data would get moved over to the "Miranda" tab. For the sake of argument lets say column D and E of that particular row would get moved over to column A of the "Miranda" tab.

Here's what I've come up with so far:

function importStudent() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var master_sheet = ss.getSheetByName("Master");
    var miranda_sheet = ss.getSheetByName("Miranda");
    var columnT = master_sheet.getRange("T1:T").getValues();
    var columnD = master_sheet.getRange("D1:D").getValues();
    var columnE = master_sheet.getRange("E1:E").getValues();
        for(var i = 0; i <= columnT; i++) {
            if (columnT[i][0] === "Miranda") {
                var miranda_row = (miranda_sheet.getLastRow() + 1);
                var active_row = SpreadsheetApp.setActiveRange(columnT[i].getRow());
                var selectionFirst = columnD.setActiveRange(active_row:columnD);
                var selectionLast = columnE.setActiveRange(active_row:columnE);
                var fullName = selectionFirst + " " + selectionLast;
                fullName.copyValuesToRange(miranda_sheet, 1, 1, miranda_row, miranda_row);
            }
        }
}

The below code is broken, and i'm looking for a little guidance in making it work. Right off, I know I have at least two issues: 1 there are too many variables, and 2 I'm not sure I can pass a variable as a parameter for the setActiveRange() class. So, my questions:

How can I clean this up? And what (class?) should I be using in order to make it functional?

PS - I still consider myself a novice when it comes to JavaScript. I have the knowledge, but the practical application is something I'm still learning :)

Upvotes: 1

Views: 1783

Answers (1)

Jason Allshorn
Jason Allshorn

Reputation: 1625

not sure if you need to use script to do what you are trying. Why not use a query formula?

For example:

=query(MasterData!:A:E,"SELECT D,E WHERE A = 'Miranda'",0)

There is some help on queries here. You can also query across spreadsheets. That is to say you could assign Miranda her own spreadsheet, which feeds from your master data.

Please share with me a sample spreadsheet data if you need more assistance.

Upvotes: 4

Related Questions