1

I have a scipt that moves data when a cell gets editet and it works perfectly.

The problem is that I want to modify the getlastrow part of the script to the second column (Column B) since I have data in Column A in the destination sheet.

I have tried to modify the getlast row with brackets for ex. getlastrow(1, 2) and I have also tried to add another "function" that specifies the second column where I want to move it wiouth success but im pretty sure I put it in the wrong part of the script.

Here is the script:

function ForecastJa1(e) {
  //e.source.toast("Entry");
  //console.log(JSON.stringify(e));
  const sh=e.range.getSheet();
  if(sh.getName()=="Casebuilder" && e.range.columnStart==13 && e.value=="Ja") {
    const tsh=e.source.getSheetByName('A');
    const nr=tsh.getLastRow();
    sh.getRange(e.range.rowStart,4,1,1).moveTo(tsh.getRange(nr,12,1,1));
    sh.getRange(e.range.rowStart,8,1,1).moveTo(tsh.getRange(nr,14,1,1));
    sh.getRange(e.range.rowStart,1,1,3).moveTo(tsh.getRange(nr,2,1,3));
    sh.getRange(e.range.rowStart,11,1,1).moveTo(tsh.getRange(nr,16,1,1));
    sh.clearcontent(e.range.rowStart);
  }
}
Mondjeu
  • 27
  • 4
  • I would really appreciate some help, trying to figure out how I can get the lastrow of column B without success – Mondjeu Mar 04 '22 at 16:25

1 Answers1

0

Suggestion

Perhaps you can try using this simple method from this similar answered post. You can try the tweaked script below:

Sample Tweaked Script:

function ForecastJa1(e) {
  //e.source.toast("Entry");
  //console.log(JSON.stringify(e));
  const sh=e.range.getSheet();
  if(sh.getName()=="Casebuilder" && e.range.columnStart==13 && e.value=="Ja") {
    const tsh=e.source.getSheetByName('A');

    //Get Last Row of Column B on sheet `A`
    var Direction=SpreadsheetApp.Direction;
    var nr =tsh.getRange("B"+(tsh.getLastRow()+1)).getNextDataCell(Direction.UP).getRow() + 1;

    sh.getRange(e.range.rowStart,4,1,1).moveTo(tsh.getRange(nr,12,1,1));
    sh.getRange(e.range.rowStart,8,1,1).moveTo(tsh.getRange(nr,14,1,1));
    sh.getRange(e.range.rowStart,1,1,3).moveTo(tsh.getRange(nr,2,1,3));
    sh.getRange(e.range.rowStart,11,1,1).moveTo(tsh.getRange(nr,16,1,1));
    sh.clearcontent(e.range.rowStart);
  }
}

Quick Test:

  • Script test:

enter image description here

  • Sample Sheet:

enter image description here

  • Log result:

enter image description here

SputnikDrunk2
  • 3,398
  • 1
  • 5
  • 17
  • @Mondjeu If there's anything missing or if I have misunderstood something, just feel free to message. I hope this answers your question. – SputnikDrunk2 Mar 04 '22 at 18:30