I am trying to Take highlighted cells and move them to the bottom of the list, then tidy the list. This is what I have so far.
function duplicateAndOrganizeActiveSheet() {
// Duplicate the sheet
var mySS = SpreadsheetApp.getActiveSpreadsheet();
var duplicateSheet = mySS.duplicateActiveSheet();
// Change the name of the new sheet.
duplicateSheet.setName(duplicateSheet.getSheetId());
duplicateSheet.setTabColor(null);
// Get the array of background color in each cell for that range
var range = duplicateSheet.getRange('A2:A')
var bkg = range.getBackgrounds();
// Take highlighted cells and move them to the bottom of the list, then tidy the list
// iterate over rows
for(var i=0;i<bkg.length;i++){
// iterate over columns
for(var j=0;j<bkg[i].length;j++){
// if the specific cell at row i and column j is mid yellow '#ffd966'
var results = [[],[]]
if(bkg[i][j]==='#ffd966') results.push([i],[j])
// Move matching cells to the bottom of the list
var end_list = duplicateSheet.getRange('A:A').getNextDataCell(SpreadsheetApp.Direction.DOWN)
var destination = duplicateSheet.getRange(end_list +':A')
destination.setValues(results)
}
}
// trim blank cells from top of list
var range2 = duplicateSheet.getRange('E2:E'+duplicateSheet.getLastRow()) // get a range start from row 2
var data = range2.getValues().filter(String); // get a data and remove empty elements
range2.clearContent().offset(0,0,data.length).setValues(data); // put the data back on the sheet
Everything out side of my loop sequence works as expected, I just don’t have enough experience to get the output working.
I started with
var results = [] if(bkg[i][j]==='#ffd966') results.push(i,j)
then googled the error message, then tried again, then googled the next error message, etc…
my current error message is
Exception: Range not found on line 21 (results.push)
Hayden Taylor is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.