1

I am trying to move the contents of column D to column A and keep them as formulas or values. The code below works but it takes FOREVER!!

I used this answer to put the values and formulas into an array: How do I copy a row with both values and formulas to an array?

I used this suggestion to separate them out based on their type: https://productforums.google.com/forum/#!topic/docs/JtcH-U3qC7s

  var ss = SpreadsheetApp.getActiveSheet();  
  var formulas = ss.getRange("D2:D").getFormulas();
  var values = ss.getRange("D2:D").getValues();
  var merge = new Array(formulas.length);
  for( var i in formulas ) {
    merge[i] = new Array(formulas[i].length);
    for( var j in formulas[i] )
      merge[i][j] = formulas[i][j] !== '' ? formulas[i][j] : values[i][j];
  }
  for (k=0;k<merge.length;k++){
    var rowRange = ss.getRange("A2");
    var str = merge[k].toString();
    var formulaChecker = str.substring(0,1);
    if (formulaChecker == "="){
      rowRange.offset(k, 0).setFormula(merge[k]);
    }else{
      rowRange.offset(k, 0).setValue(merge[k]);  
    }
  }

Because it runs so slowly I feel like I missed something.

Is there a way to make it more efficient and run faster?

Community
  • 1
  • 1
piki
  • 13
  • 3
  • 1
    One thing you might consider is instead of .getRange("D2:D"), you might want to use sheet.getLastRow(), and sheet.getLastCol() instead which should give you much less to loop though. (I am doing this from memory, and not testing, so my syntax may be off.) – Bjorn Behrendt Jan 06 '16 at 19:31
  • @BjornBehrendt on my next attempt I switched to getLastRow() and even resorted to turning everything into formulas by giving them equals signs which helped some but Sandy's solution was like R2 had turned on the hyper drive! I believe Sandy was right that the merge was taking a lot of time. Thanks all!! – piki Jan 07 '16 at 14:55

1 Answers1

0

Try this:

function copyBoth() {
  var sh = SpreadsheetApp.getActiveSheet();
  var dataLngth = sh.getLastRow();
  var rngCol_D = sh.getRange(2, 4, dataLngth, 1); //Set range for source - Example is column D

  var formulas = rngCol_D.getFormulas(); //Get all the forumlas from the source range
  var values = rngCol_D.getValues();

  var rngCol_A = sh.getRange(2, 1, values.length, 1); //Set range for destination - Example is column A

  rngCol_A.setFormulas(formulas);//Write all the formulas to the destination:

  //Write all the values, individually to each cell.
  var i = 0, thisFormula;

  for (i=0;i<dataLngth;i+=1) {
    thisFormula = formulas[i][0];
    //Logger.log('thisFormula: ' + thisFormula);
    //Logger.log('typeof thisFormula: ' + typeof thisFormula);

   if (thisFormula === "") {
      sh.getRange(i+2, 1).setValue(values[i][0]);//Write the individual value to the single cell
   };
  };
};

This code is very different from the code you are using. It sets all the formulas first. This eliminates the need to write every single cell. You still need to write values to individual cells that are in between the formulas, and that is done at the end. This example writes all the formulas at once, then the values one by one. But you could do it the opposite way. For example, if there are more values than formulas, it might save a couple of milliseconds.

This strategy also eliminates the need to merge the data, which is probably taking a lot of time.

Alan Wells
  • 30,746
  • 15
  • 104
  • 152