CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-apispreadsheet
Sean Howard
Sean Howard

Reputation: 1

Google Sheets - Script to move cells from two columns to the bottom of the next two columns

Image of my Spreadsheet Layout I am making a Sheets version of a KanBan board. Basically, all items start on the very first column, and as I complete a single task for each item, I select a dropdown on the cell next to it, and it moves over to the bottom of the next column.

My issue is that I can't figure out how the script to get the cells moved over without erasing the data in the existing cells.

I've attached a screenshot of the page that I've made for this. This is one of my latest attempts, and I've got nothing to work now. I'm going backwards in progress.

function GetDataToNextColumn() {
  var source = SpreadsheetApp.getActiveSpreadsheet();

  var lastRow = source.getLastRow();
  var mainBoard = source.getSheetByName("MainBoard")
  var target_sheet = mainBoard.getDataRange();
  var source_sheet = mainBoard.getRange("A2:B");
  var i = 1;
  
  while (i <= lastRow) {
  if (mainBoard.getRange("B"+i).getValue() == "Scheduled") {
    
    var data = mainBoard.getRange('A' + i + ':B' + i).getValues()[0];

    mainBoard.appendRow(data);
  }
  
   else {
      i++;
      }
    }
  }

Upvotes: 0

Views: 61

Answers (1)

Cooper
Cooper

Reputation: 64130

Move first to columns to bottom of next two columns

function getDataToNextColumn() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0")
  let vs = sh.getRange(2, 1, sh.getLastRow(), sh.getLastColumn()).getValues();
  let cols1_2 = vs.map(r => [r[0], r[1]]);
  sh.getRange(sh.getLastRow() + 1, 3, cols1_2.length, cols1_2[0].length).setValues(cols1_2);
  sh.getRange(2, 1, sh.getLastRow() - 1, 2).clearContent();
}

function getDataToColumn(col) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0")
  let vs = sh.getRange(2, 1, sh.getLastRow(), sh.getLastColumn()).getValues();
  let cols1_2 = vs.map(r => [r[0], r[1]]);
  sh.getRange(sh.getLastRow() + 1, col, cols1_2.length, cols1_2[0].length).setValues(cols1_2);//edited this
  sh.getRange(2, 1, sh.getLastRow() - 1, 2).clearContent();
}

Upvotes: 0

Related Questions