Google Script for getting Some Values, and Some formulas

Question

I'm working on a sheet that will get data transferred from one sheet to another. I've manipulated another script I've found and it's working pretty well. Until I need to get some cells as Values, and some as Formulas.

I need Col A-Col D as "getValues" I need Col E-Col Q as "getFormulas"

Here's the formula that was working:

function Copy(){
  var origSheetObject = SpreadsheetApp.openById('Key1').getSheetByName('Daily Log');
  var destSheetObject = SpreadsheetApp.openById('Key2').getSheetByName('Imported Data');
  var origLastCol = origSheetObject.getLastColumn();
  var arrOrigData = origSheetObject.getRange (6, 1, 1, 17).getFormulas();       
  var destlastRow = destSheetObject.getLastRow()+1;
  var cont;
  for (cont = 0; cont <origLastCol; cont++)
    destSheetObject.getRange(destlastRow, 1+cont).setValue(arrOrigData[0][cont]);

I know If I were to change the getFormula to getValue I get just values and not the formulas I need to see. Here is what I've tried but it's not correct:

function Copy(){
  var origSheetObject = SpreadsheetApp.openById('Key1').getSheetByName('Daily Log');
  var destSheetObject = SpreadsheetApp.openById('Key2').getSheetByName('Imported Data');
  var origLastCol = origSheetObject.getLastColumn();
  var arrOrigData = origSheetObject.getRange (6, 1, 1, 4).getValues();
                    origSheetObject.getRange (6, 5, 1, 13).getFormulas();       
  var destlastRow = destSheetObject.getLastRow()+1;
  var cont;
  for (cont = 0; cont <origLastCol; cont++)
    destSheetObject.getRange(destlastRow, 1+cont).setValue(arrOrigData[0][cont]);

I'm not sure how to get it to let me pull values and formulas along the same row. Any suggestions would be awesome!

Answer 1

function Copy(){
   var origSheetObject = SpreadsheetApp.openById('Key1').getSheetByName('Daily Log');
   var destSheetObject = SpreadsheetApp.openById('Key2').getSheetByName('Imported Data');
   var origLastCol = origSheetObject.getLastColumn();
   var arrOrigData = origSheetObject.getRange(1, 1, 1, 17).getValues();
   var arrOrigForm = origSheetObject.getRange(1, 1, 1, 17).getFormulas();

   for( lin in arrOrigForm )
     for( col in arrOrigForm[ i ] )
       if( arrOrigForm[ lin ][ col ] != "" )
         arrOrigData [ lin ][ col ] = arrOrigForm[ lin ][ col ];      

   var destlastRow = destSheetObject.getLastRow()+1;
   var cont;
     for (cont = 0; cont <origLastCol; cont++)
         destSheetObject.getRange(destlastRow, 1+cont).setValue(arrOrigData[0][cont]);
 }

So thats what I've put goether based on your information. And I'm very open to realizing I'm not doing this right. If you could possibly edit this code and show me what it should look liked finished I should be able to work my way through my errors and learn what I'm not able to do right now. Thank you again for helping me with this.

Answer 2

Get the values AND the formulas in different variables, then iterate trough the formula variable, if there is a formula, save that formula to the original variable, as such:

var arrOrigData = origSheetObject.getRange(1, 1, 1, 17).getValues();
var arrOrigForm = origSheetObject.getRange(1, 1, 1, 17).getFormulas();

for( lin in arrOrigForm )
  for( col in arrOrigForm[ lin ] )
    if( arrOrigForm[ lin ][ col ] != "" )
      arrOrigData [ lin ][ col ] = arrOrigForm[ lin ][ col ];

You can save this to a new function, as this

function getValuesAndFormulas( A1Range ){
  var arrOrigData = origSheetObject.getRange( A1Range ).getValues();
  var arrOrigForm = origSheetObject.getRange( A1Range ).getFormulas();

  for( lin in arrOrigForm )
    for( col in arrOrigForm[ lin ] )
      if( arrOrigForm[ lin ][ col ] != "" )
        arrOrigData [ lin ][ col ] = arrOrigForm[ lin ][ col ];

  return arrOrigData;
}

Only way to make it a oneLiner.