Google Apps Script- Trying to Efficiently Copy Noncontiguous Cells From One Sheet to a First Empty R-CodePudding

In a single Google Sheets worksheet, I have a user-input sheet that is arranged to work nicely for humans who don't like spreadsheets, and therefore has the labels interspersed with the data, and a ledger sheet for record keeping. The input sheet has a combination of user-focused calculating cells and records-focused cells. I am attempting to create a script (to be run from a button on the sheet, which already works) that copies the current contents of the records-focused cells to the ledger sheet, then resets all of the input cells for the next use. I will need the script to work for multiple copies of the input sheet, and I would prefer it not visibly jump over to the records sheet when used, but that is optional.

I have pieced together my script thus far from a macro recording and many other answers; I am not wedded to any of the current code, but the input and output sheets need to remain as they are. I do have a computational guts sheet that can be used to hold some of the values that are constants in my current code, however. I have seen some solutions to similar problems that takes the definitions of which cells to copy from/to from the spreadsheet itself, but I am not too worried about it.

Edit: Okay, I got the code considerably cleaned up, and it currently does what I need, just inefficiently.

function MakeMasterLogRecord() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sourcesheet = SpreadsheetApp.getActiveSheet();
  const masterlog = spreadsheet.getSheetByName('Master Log');

  var firstemptyrow = masterlog.getLastRow()   1;
  var currentcolumn = 1
  var targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  var sourcecells = ['Z12','C2','N2','C10','F2','C4','E4','C5','E5','L5','L4','Z13','T4','T5','C6'];

  sourcesheet.getRange('Z12').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;

  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('C2').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;

  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('N2').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;
  
  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('C10').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;

  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('F2').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;
  
  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('C4').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;
  
  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('E4').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;
  
  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('C5').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;
  
  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('E5').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;
  
  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('L5').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;
  
  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('L4').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;
  
  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('Z13').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;
  
  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('T4').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;
  
  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('T5').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;
  
  targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  sourcesheet.getRange('C6').copyTo(targetcell, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  currentcolumn  ;

};

The variable "sourcecells" is the list of cells holding the information I want to copy, and I would like to be able to use it as the input for a loop, rather than having to directly call each value in the list. It is not currently used.

Edit Again:

Used getValue and setValue as suggested by Kessy, and a forEach loop as suggested by Cooper.

This "works" in that it runs the correct number of times, albeit with the same data, and it is considerably slower than the version where I just laid out the cell references manually.

function MakeMasterLogRecord() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sourcesheet = SpreadsheetApp.getActiveSheet();
  const masterlog = spreadsheet.getSheetByName('Master Log');

  var firstemptyrow = masterlog.getLastRow()   1;
  var currentcolumn = 1
  var targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
  const sourcecell = ['\'Z12\'','\'C2\'','\'N2\'','\'C10\'','\'F2\'','\'C4\'','\'E4\'','\'C5\'','\'E5\'','\'L5\'','\'L4\'','\'Z13\'','\'T4\'','\'T5\'','\'C6\''];

  for (const element of sourcecell) {
    var targetcell = masterlog.getRange(firstemptyrow, currentcolumn, 1, 1);
    targetcell.setValue(sourcesheet.getRange('Z12').getValue());
    currentcolumn  ;
  };

};

I have tried the array entries with and without the escaped quotes, but both result in Range Not Found errors. I can't get the escaped quotes to work properly around the "sourcecell" variable, so I don't know if it would work if I did it that way. It always considers the word "sourcecell" to be inside the quotes.

CodePudding user response：

Try this to improve performance a bit:

function submitData() {
  const ss = SpreadsheetApp.getActive();
  const formRangeList = ss.getActiveSheet()
    .getRangeList(['Z12', 'C2', 'N2', 'C10', 'F2', 'C4', 'E4', 'C5', 'E5', 'L5', 'L4', 'Z13', 'T4', 'T5', 'C6']);
  ss.getSheetByName('Master Log')
    .appendRow(formRangeList.getRanges().map(range => range.getValue()));
  formRangeList.clearContent(); // remove this line if you do not want to clear the "form"
}

This is still far from optimal, because it calls Range.getValue() 15 times. To improve runtime performance tenfold, use .getRange('A1:Z13').getValues() to get all the data in one go, and then use array indexing to extract the values you need and append to the target sheet, like this:

function submitDataOhSoMuchFaster() {
  const ss = SpreadsheetApp.getActive();
  const values = ss.getActiveSheet().getRange('A1:Z13').getValues();
  const spec = [
    { column: 26, row: 12 }, // Z12
    { column: 3, row: 2 }, // C2
    { column: 14, row: 2 }, // N2
    // ...
  ];
  ss.getSheetByName('Master Log')
    .appendRow(spec.map(cell => values[cell.row - 1][cell.column - 1]));
}

See Apps Script best practices.