Last active
April 27, 2020 16:51
-
-
Save jhned/b7981d03eed6a971fe7f71a204b39671 to your computer and use it in GitHub Desktop.
Filter Schema Properties Based on Type
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// onEdit is a simple trigger | |
// https://developers.google.com/apps-script/guides/triggers | |
function onEdit(e) { | |
// Use Logger to log values, e.g., Logger.log(e) | |
// https://developers.google.com/apps-script/guides/logging | |
if ('' === e.value ) { // If the cell was cleared out, don't do anything. | |
return false; | |
} | |
// Working with range: https://developers.google.com/apps-script/reference/spreadsheet/range | |
if (4 !== e.range.getColumn()) { // Looking for changes to column D. | |
return false; | |
} | |
if (1 < e.range.getNumColumns() || 1 < e.range.getNumRows()) { // If more than one column is effected, bail out. | |
return false; | |
} | |
// Get the Type value that's been set. | |
let value = e.value; | |
// Next, we're looking to get the available Properties for the selected Type. | |
// These are found in Column G of the Types Sheet, where Column B is the Type Name. | |
// So the plan is: get the Type value, find that Type value in Column B of the Types Sheet, then get the contents of Column G. | |
const spreadsheet = SpreadsheetApp.getActive(); | |
const targetSheet = spreadsheet.getSheetByName("Types"); | |
// This gets all the values for Column B, then finds the index of the value. | |
const typesRow = targetSheet.getRange(1, 2, 1129).getValues().findIndex(function(r){return r[0] === value;}) + 1; | |
// This gets all the values for Column G, then removes the Schema.org URL. | |
let typesRowProperties = targetSheet.getRange(typesRow, 7).getValues()[0][0]; | |
let cleanedProperties = typesRowProperties.replace(/http:\/\/schema.org\//g, ''); | |
// We need to know how many cells to which to apply this validation. In order to do that, we have to start at the row that changed, | |
// then go down the column until we find another value. If no value is found, we use getLastRow. | |
// Get the row where the value was changed. | |
let row = e.range.getRow(); | |
// Get all the rows below this one. | |
// Get all the cells in this column. | |
const column = spreadsheet.getRange('D:D'); | |
// Then get the values. | |
const values = column.getValues(); | |
// Use findIndex to find the index of the next filled row. | |
let nextFilledRow = values.findIndex(function(r, index){ | |
if ( index <= row - 1 ) { | |
return false; | |
} | |
if ( '' === r[0] ) { | |
return false; | |
} | |
return true; | |
}); | |
// If no rows were found, then we use the last row with data. | |
if (-1 === nextFilledRow) { | |
nextFilledRow = spreadsheet.getLastRow(); | |
} else { | |
nextFilledRow++; // findIndex starts at 0. If we have a valid response from that, we need to add 1. | |
} | |
// We can define our range for data validation: the row to start on, the column (E), and then the number of rows. | |
const cellsForDataValidation = spreadsheet.getSheetByName('Schemas').getRange(row, 5, nextFilledRow - row); | |
// Define and build the data validation. | |
const propertiesRule = SpreadsheetApp.newDataValidation().requireValueInList(cleanedProperties.split(","), true).build(); | |
cellsForDataValidation.setDataValidation(propertiesRule); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment