CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsspreadsheet
Jensen010
Jensen010

Reputation: 445

Copy cell values from one google sheet to another based on condition

I'm having an issue with a script for Google sheets

I work in education, and basically we have some teachers who would like to have a form to use when they need to refer a student to an administrator.

So, we have a Google form set up and it's answers populate our "Master" tab within the sheet. Then we have a tab set up for each administrator, the idea being that we can write a function that moves data over based on the contents of the "administrator assigned" box (ColumnT in the code below)

The first bit I need to get functional is the code that handles that part, here's what I've been able to come up with (Heavily commented so that you can hopefully see my thought process):

//Get the active spreadsheet and store in a variable
function importStudName() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
//Get"Master" spreadsheet and store in a variable
var master_sheet = ss.getSheetByName("Master");
//Get "Miranda" spreadhseet and store in a variable
var miranda_sheet = ss.getSheetByName("Miranda");
//Column T
var columnT = master_sheet.getSheetValues(0, 20, 0, -1);
//Column D
var columnD = master_sheet.getSheetValues(0, 4, 0, -1);
//Column E
var columnE = master_sheet.getSheetValues(0, 4, 0, -1);
//Loop through Column T and find every value that = "Miranda"
    for(var i = 0; i <= columnT; i++) {
        if (columnT[i] === "Miranda") {
          //Set a variable to hold the last row in the "Miranda" sheet
            var miranda_row = (miranda_sheet.getLastRow() + 1);
          //Make the row with the "Miranda" string active

          //Set a variable that concatenates the Column D and Column E variables (from "master" sheet) into a string

          //Copy the new variable to the "Miranda" sheet
            .copyValuesToRange(miranda_sheet, 1, 1, miranda_row, miranda_row);
    }
  }
}

Mostly, I can't seem to figure out the bit that would make select the row that has the "Miranda" keyword in it. Once I had that down, I think it's just a matter of using the .getRange() function to pull the values from columnD and columnE.

I'm a bit rusty on my JavaScript, and this is the first time I've written something for apps script. So it's possilble I'm going about this all wrong :)

Any suggestions would be welcome

Upvotes: 0

Views: 2220

Answers (1)

user6655984
user6655984

Reputation:

The method getSheetValues returns "Object[][] — a two dimension array of values". So, its entries should be accessed as values[i][j] where i is row offset from the upper left corner of the range, and j is the column offset. In particular, your Miranda comparison should be

if (columnT[i][0] === "Miranda")

Another remark: the row and column numbers in Sheets begin with 1, unlike JavaScript array indices. Passing (0, 20, 0, -1); in getSheetValues indicates you may be miscounting things.

Finally, I'd recommend using .getRange(...).getValues() instead of getSheetValues(). For one thing, you can pass A1 notation to getRange, like

var valuesT = sheet.getRange("T1:T").getValues();

to get all values in column T. If it's preferable to not fetch a bunch of empty cells at the bottom, one can use

var lastRow = sheet.getLastRow();
var valuesT = sheet.getRange("T1:T" + lastRow).getValues();

Upvotes: 2

Related Questions