Google apps script - copy non-blank cells from one column to another

I am trying to copy only the non-blank cells from one range of cells (part of a column) to another in Google Sheets. My existing code (including extra bits for skipping over weekends) is below.

function copyNotBlank() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.setActiveSelection("B4"); // Cell B4 contains the column number for today
  var columnNumber = sheet.getActiveCell().getValue();
  selectToday(); // function that selects a cell containing the day letter for today
  dayLetter = sheet.getActiveCell().getValue();
  if (dayLetter == "M") {
    sourceColumnNumber = columnNumber - 3;
  } else {
    sourceColumnNumber = columnNumber - 1;
  }
  if (sourceColumnNumber < 6) {
    ui.alert("Error","Cannot copy from before the start of the current FY.",ui.ButtonSet.OK);
  }
  var lastRow = sheet.getLastRow();
  for (row = 6; row <= lastRow; row++) {
    var sourceValue = sheet.getRange(row, sourceColumnNumber).getValue();
    if (sourceValue != '') {sheet.getRange(row, columnNumber).setValue(sourceValue)};
  }
}

It is a sheet I use for recording equipment usage and scheduling equipment availability, and while the script does what I want, it does it incredibly slowly. It may be due to making individual server calls for each cell, or due to the conditional formatting in my sheet, or something else, I don't know.

Is there a way I can speed it up? I have seen the use of batch requests mentioned in another post to reduce the number of server calls, but I'm not sure how to implement this, or even if it would work for my application. I've also looked for a way to suspend sheet updates until the code has finished, but I've been unsuccessful here too.

I also have a similar function that copies the range in one go rather than looping through each cell, using .getValues() and .setValues() with a number of rows parameter in the .getRange() arguments, but this also copies blank cells, overwriting anything that is already in the cell. I don't mind overwriting the cell if if I'm copying something else into it, but I want it left alone if the source cell is empty.

Any suggestions?

Solution

I believe your goal is as follows.

Your showing script works. But, you want to reduce the process cost of the script.

When I saw your script, it seems that getValue and setValue are used in a loop. In this case, the process cost will be high. Ref (Author: me)

In this case, first, the values are retrieved from Spreadsheet and processed the retrieved values, and then, the updated values are put into the Spreadsheet. When this flow is reflected in your script, how about the following modification?

From:

for (row = 6; row <= lastRow; row++) {
  var sourceValue = sheet.getRange(row, sourceColumnNumber).getValue();
  if (sourceValue != '') {sheet.getRange(row, columnNumber).setValue(sourceValue)};
}

To:

var values = sheet.getRange(6, 1, lastRow - 5, sheet.getLastColumn()).getValues();
var dstValues = values.map((r, i) => [r[sourceColumnNumber - 1] != "" ? r[sourceColumnNumber - 1] : values[i][columnNumber - 1]]);
sheet.getRange(6, columnNumber, dstValues.length).setValues(dstValues);

Or, when Sheets API is used, please modify it as follows. In this case, please enable Sheets API at Advanced Google services.

var values = sheet.getRange(6, sourceColumnNumber, lastRow - 5).getValues().map(([r]) => [r != "" ? r : null]);
Sheets.Spreadsheets.Values.update({ values }, sheet.getParent().getId(), `'${sheet.getSheetName()}'!${sheet.getRange(6, columnNumber).getA1Notation()}`, { valueInputOption: "USER_ENTERED" });

When Sheets API is used, only the destination cells can be updated.

Note:

The whole modified script is as follows.

function copyNotBlank() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.setActiveSelection("B4");
  var columnNumber = sheet.getActiveCell().getValue();
  selectToday();
  dayLetter = sheet.getActiveCell().getValue();
  if (dayLetter == "M") {
    sourceColumnNumber = columnNumber - 3;
  } else {
    sourceColumnNumber = columnNumber - 1;
  }
  if (sourceColumnNumber < 6) {
    ui.alert("Error", "Cannot copy from before the start of the current FY.", ui.ButtonSet.OK);
  }
  var lastRow = sheet.getLastRow();
  
  // I modified the below script.
  var values = sheet.getRange(6, 1, lastRow - 5, sheet.getLastColumn()).getValues();
  var dstValues = values.map((r, i) => [r[sourceColumnNumber - 1] != "" ? r[sourceColumnNumber - 1] : values[i][columnNumber - 1]]);
  sheet.getRange(6, columnNumber, dstValues.length).setValues(dstValues);

  // or, please use the following script.
  // var values = sheet.getRange(6, sourceColumnNumber, lastRow - 5).getValues().map(([r]) => [r != "" ? r : null]);
  // Sheets.Spreadsheets.Values.update({ values }, sheet.getParent().getId(), `'${sheet.getSheetName()}'!${sheet.getRange(6, columnNumber).getA1Notation()}`, { valueInputOption: "USER_ENTERED" });
}

Google apps script - copy non-blank cells from one column to another

From:

To:

Note:

References: