Is there an alternative to appendRow in AppsScript that only prints certain columns while leaving ot-CodePudding

I'm building a calculator to use for pricing purposes. It has a primary "Calculator" sheet, where an admin can enter data and then generate a new result to the "DataLog" sheet. The "DataLog" sheet stores the results (columns A through X) and calculates the resulting price (columns Y through AO). There are also a few workflow columns that need to be present for each row (Columns AP through AS).

I am currently using appendRow() to print the data to the "DataLog" sheet. The issue is that appendRow() finds the first empty row, and since columns Y through AS are not empty because they contain necessary formulas/workflow, it prints to the bottom of the sheet. I am looking for a way to print the data where 1) it checks only a certain column for an empty row (column A or C, for example) and prints to that row, and 2) does not overwrite the formula/workflow columns (Y through AS).

Is there a way to do this using appendRow() or is there another function I should be using? Other than this one issue of where to print the results, everything works just as I want it to, but I cannot seem to find a way to resolve this issue.

EDIT: The reason the formula and workflow must be present within "DataLog" is that there are situations where after an entry has been filled out and printed changes need to be made to row, thereby changing the final price. So I cannot calculate the price within the function and print that as a static number.

Here is a copy of the calculator: https://docs.google.com/spreadsheets/d/1vsVZeOUUqhdiW1unz6dPuiP5yw24ENrv1-49kXqBnx4/edit#gid=0

Here is a copy of the code I am using:

function ClearCells() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('CALCULATOR');
  sheet.getRange('G9:H9').clearContent();
  sheet.getRange('G11').clearContent();
  sheet.getRange('G14:H14').clearContent();
  sheet.getRange('G6').clearContent();
  sheet.getRange('I6').clearContent();
  sheet.getRange('I17:I21').clearContent();
  sheet.getRange('I24:I29').clearContent();
  sheet.getRange('I32').clearContent();
  sheet.getRange('K5').clearContent();
  sheet.getRange('K15').clearContent();
}

function FinalizePrice() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sourceRangeFL = ss.getRangeByName('FirstLast');
  const sourceValsFL = sourceRangeFL.getValues().flat();
  const sourceRangeEN = ss.getRangeByName('EntityName');
  const sourceValsEN = sourceRangeEN.getValues().flat();
  const sourceRangeEP = ss.getRangeByName('EmailPhone');
  const sourceValsEP = sourceRangeEP.getValues().flat();
  const sourceRangeRT = ss.getRangeByName('ReturnType');
  const sourceValsRT = sourceRangeRT.getValues().flat();
  const sourceRangeRE = ss.getRangeByName('Returning');
  const sourceValsRE = sourceRangeRE.getValues().flat();
  const sourceRangeBQ = ss.getRangeByName('BasicQuestions');
  const sourceValsBQ = sourceRangeBQ.getValues().flat();
  const sourceRangeSEQ = ss.getRangeByName('SchEQuestions');
  const sourceValsSEQ = sourceRangeSEQ.getValues().flat();
  const sourceRangeEQ = ss.getRangeByName('EntityQuestions');
  const sourceValsEQ = sourceRangeEQ.getValues().flat();
  const sourceRangePYP = ss.getRangeByName('PYP');
  const sourceValsPYP = sourceRangePYP.getValues().flat();
  const sourceRangeADJ = ss.getRangeByName('Adjustment')
  const sourceValsADJ = sourceRangeADJ.getValues().flat();
  const sourceRangeAN = ss.getRangeByName('AdjustmentNote')
  const sourceValsAN = sourceRangeAN.getValues().flat();


  

  const sourceVals = [...sourceValsFL, ...sourceValsEN, ...sourceValsEP, ...sourceValsRT, ...sourceValsRE, ...sourceValsBQ, ...sourceValsSEQ, ...sourceValsEQ, ...sourceValsPYP, ...sourceValsADJ, ...sourceValsAN]

  console.log(sourceVals)

  const anyEmptyCell = sourceVals.findIndex(cell => cell === "");
  if(anyEmptyCell !== -1){
    const ui = SpreadsheetApp.getUi();
    ui.alert(
      "Input Incomplete",
      "Please enter a value in ALL input cells before submitting",
      ui.ButtonSet.OK
    );
    return;
  }

  const date = new Date();
  const email = Session.getActiveUser().getEmail();

  const data = [date, email, ...sourceVals];

  const destinationSheet = ss.getSheetByName("DataLog");
  destinationSheet.appendRow(data);
  console.log(data);

  sourceRangeFL.clearContent();
  sourceRangeEN.clearContent();
  sourceRangeEP.clearContent();
  sourceRangeRT.clearContent();
  sourceRangeRE.clearContent();
  sourceRangeBQ.clearContent();
  sourceRangeSEQ.clearContent();
  sourceRangeEQ.clearContent();
  sourceRangePYP.clearContent();
  sourceRangeADJ.clearContent();
  sourceRangeAN.clearContent();


  ss.toast("Success: Item added to the Data Log!");

}

CodePudding user response：

I know this is incomplete but for the purpose of discussion here's how I would clear content in your situation.

function ClearCells() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  sh.getRangeList(["G9", "G11", "G14:H14", "G6", "I6", "I17:I21", "I24:I29", "I32", "K5", "K15"]).getRanges().forEach(r => r.clearContent();)
}

If you wished to append the values of your individual ranges into a row you could do it like this:

function appendRangeValues() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const osh = ss.getSheetByName('Sheet1')
  const rgl = sh.getRangeList(["G9", "G11", "G14:H14", "G6", "I6", "I17:I21", "I24:I29", "I32", "K5", "K15"]);
  const rglb = breakUpRangeList(ss,sh,rgl);
  const vs = rglb.getRanges().map(r => r.getValue());
  Logger.log(JSON.stringify(vs))
  osh.getRange(osh.getLastRow()   1, 1, 1, vs.length).setValues([vs]);
}

But I'm guessing that you want to skip over cell functions and other columns so let me know what you want and may be we can find a solution that fits your needs

The breakUpRangeList function is something I wrote a while back to break up ranges into their individual cells which I find easier to deal with.

function breakUpRangeList(ss=SpreadsheetApp.getActive(),sh=ss.getSheetByName("Sheet0"),rgl) {
  let b = [];
  rgl.getRanges().forEach(rg => {
    rg.getValues().forEach((r,i) => {
    let row = rg.getRow()   i;
    r.forEach((c, j) => {
      let col = rg.getColumn()   j;
      b.push(sh.getRange(row, col).getA1Notation())
    })
  })
})
  b = [...new Set(b)];
  Logger.log(JSON.stringify(b));
  return sh.getRangeList(b);
}

Try this:

function appendRangeValues() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const osh = ss.getSheetByName('Sheet1')
  const rgl = sh.getRangeList(["G9", "G11", "G14:H14", "G6", "I6", "I17:I21", "I24:I29", "I32", "K5", "K15"]);
  const rglb = breakUpRangeList(ss,sh,rgl);
  const vs = rglb.getRanges().map(r => r.getValue());
  Logger.log(JSON.stringify(vs))
  osh.getRange(getColumnHeight(3,osh,ss)   1, 1, 1, vs.length).setValues([vs]);
}

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  var rcA = [];
  if (sh.getLastRow()){ rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
  let s = 0;
  for (let i = 0; i < rcA.length; i  ) {
    if (rcA[i].toString().length == 0) {
      s  ;
    } else {
      break;
    }
  }
  return rcA.length - s;
}