Google Script - Assigning A Cell Value Based on Another Cell
When working with our counselors on an online scholarship form, we wanted a single question to ask which scholarships the students were applying for but then in our data spreadsheet we wanted that information separately so that we could score the applications appropriately for each scholarship type.
Because we used a checkbox question on the form for students to select which scholarships they were applying for, in the results spreadsheet we ended up with what could be multiple answers separated by commas in a single cell. I looked at several ways to separate this data out into individual cells, but in the end a short script used multiple times worked best. This ensured that there was consistency in how the data is displayed (using a split function did not do this as their first selection appeared in the first column, and not all of them selected the same scholarships).
The script I created looked for a match in the cell, then applied a set value in another cell if the match was found. Here's the script -
function nontraditional(){
var ss = SpreadsheetApp.getActiveSheet();
var data = ss.getDataRange().getValues(); // read all data in the sheet
for(n=0;n<data.length;++n) {
if(data[n][5].toString().match("Non")){ data[n][21] = 'Non Traditional Scholarship'};
elseif(data[n][5].toString().match("Superintendent")){ data[n][22] = 'Superintendents Scholarship'};
}
Logger.log(data)
ss.getRange(1,1,data.length,data[1].length).setValues(data); // write back to the sheet
}
Let's break this down -
The first 2 lines are getting the data from the active spreadsheet -
var ss = SpreadsheetApp.getActiveSheet();
var data = ss.getDataRange().getValues(); // read all data in the sheet
Next I established a variable n that started at 0 and went to the value of the number of rows of data, that would be checked incrementally -
for(n=0;n<data.length;++n)
For each n, I had it check a specific column (my 6th column as this is a case where counting starts at 0), for a match of "Non" - if it matched, it would assign the value of "Non Traditional Scholarship" to the 22nd value in the array.
{
if(data[n][5].toString().match("Non")){ data[n][21] = 'Yes'};
}
This part can be used multiple times for the different values that are in the cell from the possible choices that were selected in the form checkbox using elseif -
elseif(data[n][5].toString().match("Superintendent")){ data[n][22] = 'Yes'};
}
The last 2 lines are writing this array value back onto the spreadsheet:
Logger.log(data)
ss.getRange(1,1,data.length,data[1].length).setValues(data); // write back to the sheet
This helped us get columns that were unique to the specific scholarships, so we could continue the specific scholarship application process only with students that applied for the scholarship.
Because we used a checkbox question on the form for students to select which scholarships they were applying for, in the results spreadsheet we ended up with what could be multiple answers separated by commas in a single cell. I looked at several ways to separate this data out into individual cells, but in the end a short script used multiple times worked best. This ensured that there was consistency in how the data is displayed (using a split function did not do this as their first selection appeared in the first column, and not all of them selected the same scholarships).
The script I created looked for a match in the cell, then applied a set value in another cell if the match was found. Here's the script -
function nontraditional(){
var ss = SpreadsheetApp.getActiveSheet();
var data = ss.getDataRange().getValues(); // read all data in the sheet
for(n=0;n<data.length;++n) {
if(data[n][5].toString().match("Non")){ data[n][21] = 'Non Traditional Scholarship'};
elseif(data[n][5].toString().match("Superintendent")){ data[n][22] = 'Superintendents Scholarship'};
}
Logger.log(data)
ss.getRange(1,1,data.length,data[1].length).setValues(data); // write back to the sheet
}
Let's break this down -
The first 2 lines are getting the data from the active spreadsheet -
var ss = SpreadsheetApp.getActiveSheet();
var data = ss.getDataRange().getValues(); // read all data in the sheet
Next I established a variable n that started at 0 and went to the value of the number of rows of data, that would be checked incrementally -
for(n=0;n<data.length;++n)
For each n, I had it check a specific column (my 6th column as this is a case where counting starts at 0), for a match of "Non" - if it matched, it would assign the value of "Non Traditional Scholarship" to the 22nd value in the array.
{
if(data[n][5].toString().match("Non")){ data[n][21] = 'Yes'};
}
This part can be used multiple times for the different values that are in the cell from the possible choices that were selected in the form checkbox using elseif -
elseif(data[n][5].toString().match("Superintendent")){ data[n][22] = 'Yes'};
}
The last 2 lines are writing this array value back onto the spreadsheet:
Logger.log(data)
ss.getRange(1,1,data.length,data[1].length).setValues(data); // write back to the sheet
This helped us get columns that were unique to the specific scholarships, so we could continue the specific scholarship application process only with students that applied for the scholarship.
