I am going to post a function called moveNames() this functions main object is to move or shift names to the right unless the right is past the allowed columns or past the allowed rows. Think of a table and in this table there is 5 columns and 5 rows the objective is to move the items to the right.
If a name is in A1 I need to move it to B1. If a name is in G1 I need to move this item to A2.
I have this main functionality already working in this function. The problem is that when I tried to add notes in the mix the app does not change the note when the note is of a previous value. Meaning I can set a note if I am just setting the not to some string, but when I try to use an existing note and replace another existing note with the first it doesn’t replace it.
Here is the function:
function moveNames() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Schedule');
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Admin').getRange(6, 2).setValue("yes");
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Admin').getRange(5, 2).setValue("Running");
var startingColumn = 2; // Column B
var endingColumn = 7; // Column G
var rows = [36, 38, 40, 42, 44, 46, 48, 50]; // rows with names
// sheet.getRange(rows[0], startingColumn).setNote("HERE IS A SAMPLE NOTE");
var data = sheet.getRange(3, startingColumn, rows[rows.length-1], endingColumn).getValues(); // 2D array containing all the data
var lastRowWithTable = -1;
for (var i = rows.length - 1; i >= 0; i--) {
var nameRow = rows[i];
if (data[nameRow-3].join().trim() !== '') { // finds the last non-empty row with table
lastRowWithTable = nameRow;
break;
}
}
var lastRowTable = sheet.getRange(rows[i]-1, startingColumn);
var lastRowColumn = 0;
var lastRowRow = 0;
for(var i = 0; i < rows.length; i++){
for(var j = startingColumn; j < endingColumn; j++) {
var rowAbove = sheet.getRange(rows[i] -1, j);
if(rowAbove.getValue() != ''){
lastRowTable = rowAbove;
lastRowColumn = j-1;
lastRowRow = rows[i];
}
// console.log(rowAbove.getValue())
}
}
console.log(lastRowTable.getValue());
console.log(lastRowRow)
console.log(lastRowColumn)
var curr = null;
var note = null;
for(var i = 0; i < rows.length; i++){
for(var j = startingColumn; j < endingColumn; j++){
//check first that the first item has a value later
if(curr == null){
curr = sheet.getRange(rows[i], j).getValue(); //current cell starting will be B4 shift this value to the right as long as there is a table name to the right
note = sheet.getRange(rows[i], j).getNote();
console.log("INITIAL NOTE: ", note);
}
if(rows[i] == lastRowRow && j == lastRowColumn+1){
//add the curr to the first then end the program
// var currValue = curr.getValue();
// console.log("Value to change to: ", curr)
// console.log("VALUE GETTING CHANGED: ", sheet.getRange(rows[0], startingColumn).getValue());
sheet.getRange(rows[0], startingColumn).setValue(curr);
sheet.getRange(rows[0], startingColumn).setNote(note);
SpreadsheetApp.flush()
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Admin').getRange(5, 2).setValue("Completed");
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Admin').getRange(7, 2).setValue(new Date());
return;
}
console.log(curr);
console.log(note);
console.log("HERE IS J: ", j)
console.log("HERE IS THE ROW: ", rows[i])
var rowAboveRight = sheet.getRange(rows[i] -1, j+1);
var pointerForRowAbove = j+2;
var columnThatNextNameIsOn = j+1;
while(rowAboveRight.getValue() == '' || (rows[i] == 26 && pointerForRowAbove >= 7)){
console.log("Column: ", pointerForRowAbove)
console.log("ROW: ", rows[i])
rowAboveRight = sheet.getRange(rows[i] -1, pointerForRowAbove)
columnThatNextNameIsOn = pointerForRowAbove;
pointerForRowAbove++;
if(pointerForRowAbove > endingColumn){ //meaning that we have exhausted all columns in this particular row
i++; //move to the next row
pointerForRowAbove = startingColumn;
continue;
}
}
var tempRight = sheet.getRange(rows[i], columnThatNextNameIsOn).getValue();
var tempRightNote = sheet.getRange(rows[i], columnThatNextNameIsOn).getNote();
// var right = sheet.getRange(rows[i], columnThatNextNameIsOn)
if(j+1 != columnThatNextNameIsOn){
j = columnThatNextNameIsOn-1;
}
// var currValue = curr;
console.log("Value to change to: ", curr)
console.log("Value to change to: ", note)
console.log("VALUE GETTING CHANGED: ", sheet.getRange(rows[i], columnThatNextNameIsOn).getValue());
console.log("NOTE GETTING CHANGED: ", sheet.getRange(rows[i], columnThatNextNameIsOn).getNote());
sheet.getRange(rows[i], columnThatNextNameIsOn).setValue(curr);
sheet.getRange(rows[i], columnThatNextNameIsOn).setNote(tempRightNote);
curr = tempRight;
note = tempRightNote;
}
}
}