So I have 2 workbooks. Source (my dashboard) and Destination sheet. The destination sheet is shared externally and my goal is to send a row's data from Source to Destination whenever the data is ready (I have this manually done to ensure less errors). I have an existing code which works okay but the issue is that it appends to the next totally empty row and not the next row where column a is empty.
I have a formula waiting on column H that will do a VLOOKUP and the issue I'm facing is that it detects that row to be not empty hence it appends to the bottom of the sheet where there's totally no data or formulas. Sorry I hope it makes sense.
function sendtoDESTSH() {
const datawb = SpreadsheetApp.getActiveSpreadsheet();
const datash = datawb.getActiveSheet();
var rowIndex = datash.getCurrentCell().getRow();
var bkvalues = datash.getRange(rowIndex, 2, 1, 6).getValues().flat(); /*gets the data of cells B to G from source sheet*/
const destsh = SpreadsheetApp.openById('10pR9h.......').getSheetByName("DATA");
destsh.appendRow(bkvalues);
}
I tried using ARRAYFORMULA on Column H so it will think that the row is empty but it doesn't work.
I'm pretty sure .appendROW is the issue or that I'm missing something that will indicate what's the last row on the sheet but I've tried several workarounds I found in StackOverflow (such as this) but I couldn't get it to work?
