CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
onit
onit

Reputation: 2372

Get Selected Row's Specific Col Value via script (Google Sheets)

I've been trying to get this one to work without success so far. I need to get the TaskNumber on the first column of the row I'm on and bring it to the destination sheet, so that I can update it there. I have the following, which I'm tweaking to achieve my goal, but I guess I've bumped into my limitation walls:

function jump() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var TaskNumberSheet = ss.getSheetByName('To-Do List');
  var TaskNoRow = TaskNumberSheet.getActiveCell();
  var TaskNoCol = TaskNoRow.getColumn() == 1
  var TaskNo = TaskNoCol.getValue;
  var sheet = ss.getSheetByName('Updates');
  //var Tasks = ss.getSheetByName("To Do List").getActiveRange(Tasks.getColum(1)).getValue();

  var values = sheet.getRange("B:B").getValues();
  var maxIndex = values.reduce(function(maxIndex, row, index) {
    return row[0] === "" ? maxIndex : index;
  }, 0);
  sheet.setActiveRange(sheet.getRange(maxIndex + 2,2)).setValue(TaskNo);
}

Any help is appreciate.

Cheers, A

Upvotes: 1

Views: 2221

Answers (2)

Iamblichus
Iamblichus

Reputation: 19309

If I understood you correctly, you want to:

  1. Get the value in column A from the currently active row (in sheet To-Do List).
  2. Find the first empty cell in column B (in sheet Updates) (start looking at row #8).
  3. Copy the value that was retrieved in step 1 to the cell retrieved in step 2.
  4. Set the cell retrieved in step 2 as the active cell.

If that's the case, you can do the following:

function jump() {
  var ss = SpreadsheetApp.getActive();
  // Step 1:
  var TaskNumberSheet = ss.getSheetByName('To-Do List');
  var TaskNoRow = TaskNumberSheet.getActiveCell().getRow();
  var TaskNoCol = 1;
  var TaskNo = TaskNumberSheet.getRange(TaskNoRow, TaskNoCol).getValue();
  // Step 2:
  var sheet = ss.getSheetByName('Updates');
  var firstRow = 8;
  var column = 2;
  var numRows = sheet.getLastRow() - firstRow + 1;
  var values = sheet.getRange(firstRow, column, numRows).getValues().map(function(value) { 
    return value[0] 
  });
  var i = 0;
  for (i; i < values.length; i++) {
    if (values[i] === "") break;
  }
  var targetRange = sheet.getRange(i + firstRow, column);
  targetRange.setValue(TaskNo); // Step 3
  sheet.setActiveRange(targetRange); // Step 4
}

Upvotes: 2

Cooper
Cooper

Reputation: 64040

function jump() {
  var TargetRow=?;//Fillin target row
  var TargetCol=?;//Fillin target column
  var ss=SpreadsheetApp.getActive();
  var TaskNumberSheet=ss.getSheetByName('To-Do List');
  var TaskNoRow=TaskNumberSheet.getActiveCell().getRow();//Getting row from active cell
  var TaskNoCol=1
  var TaskNo=TaskNumberSheet.getRange(TaskNoRow,TaskNoCol).getValue();
  ss.getSheetByName('Updates').getRange(targetRow,targetCol).setValue(TaskNo);
}

Upvotes: 1

Related Questions