Copy and Paste data into next empty row

Question

I have a system generated spreadsheet that enters blank cells when there is more than one item inside an order number. For example, if Order 1 had three items, I would have three rows completely filled but only one with date. The header is like this:

Order Number | User | Date | Item

I Attached an example image from a sample dataset:

I tried the following code which does the job greatly but I couldn't finish the task because the execution is timing out every time the script is run (it stops around row number 1,000). My spreadsheet has about 3,000 rows and growing slowly. I read some instructions about trying to make the code more efficient but for me it is already very efficient (at least comparing it to VBA). Any assistance is appreciated!

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var last = sheet.getLastRow();// get last row number
 
  for (i = 2; i < last; i++) { 
    var info = sheet.getRange(i, 3);
    Logger.log(info);
 
    if (info.isBlank()) {
      var data = sheet.getRange(i-1, 3).getValues(); //copy value from cell above with date
      sheet.getRange(i, 3).setValues(data); //paste value on empty cell
    } 
  }
}

Answer 1

I think that the reason of your issue is that getValues and setValues are used in the loop. By this, the process cost will be high. This has already been mentioned by Rubén's answer. So in order to reduce the cost, in your situation, how about the modification with the following flow?

1. Retrieve values from the column "C" using getValues.
2. Create an array for putting values to Spreadsheet.
3. Put the created array to the Spreadsheet using setValues.

When above flow is reflected to your script, it becomes as follows.

Modified script:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var last = sheet.getLastRow();// get last row number

  // I modified below script.
  var range = sheet.getRange("C2:C" + last);

  // 1. Retrieve values from the column "C".
  var values = range.getValues();

  // 2. Create an array for putting values to Spreadsheet.
  var temp = "";
  var v = values.map(([c]) => [c.toString() != "" ? temp = c : temp]);

  // 3. Put the created array to the Spreadsheet.
  range.setValues(v);
}

References:

• getValues()
• setValues(values)
• map()

Answer 2

The problem is that getRange and other similar Spreadsheet Services (Class SpreadsheetApp) are "very" slow.

Instead of reading one range at each for iteration read the whole data range at once by using getDataRange() then getValues() then iterate over the Array that has all the data range values.

Related

• Long processing time likely due to getValue and cell inserts
• Slow Archiving Google Apps Script

Answer 3

Try this:

function myFunction() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet()
  var shsr=2;//data start row
  var vs=sh.getRange(shsr,3,sh.getLastRow()-shsr+1,1).getValues();
  vs.forEach(function(r,i){if(!r[0]){vs[i][0]=vs[i-1][0];}});
  sh.getRange(shsr,3,sh.getLastRow()-shsr+1,1).setValues(vs);
}