Google Sheets appendRow with formula values

Question

My goal is to write a script in Google Sheets that will copy two (or more) values from specific cells on a single row on a worksheet to the bottom of the list on another. I need to copy the actual values as well as calculate additional from the original values.

This is my first attempt and should explain what I am trying to do:

   function CopyCals() {
   var spreadsheet = SpreadsheetApp.getActive();
   spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
  spreadsheet.appendRow(['=today()','','=Sheet1!B30','=Sheet1!E30','=Sheet1!B30-Sheet1!E30','=Sheet1!B30-(Sheet1!E30-2500)']);
      };

This works great, except it appends the actual formulas, rather than the values of the fields. As the fields change, so do the values of what was copied.

To work around this, I tried getRange but I can't figure out how to select specific elements (i.e. the values of C30 and E30) in the array. Below is what I tried:

function copyCals2() {
  var spreadsheet = SpreadsheetApp.getActive();
  var range = spreadsheet.getRange("B30:E30").getValues();
  spreadsheet.appendRow(range[0]);
  };

How do I get appendRow to only print the values for C30 and E30 and use them in formulas as shown in my original attempt.

Cooper Cooper · Accepted Answer · 2019-03-11T00:57:40

Getting the desired data

function CopyCals() {
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName('Sheet2');
  sh2.appendRow([new Date(),'',sh1.getRange('B30').getValue(),sh1.getRange('E30').getValue(),sh1.getRange('B30').getValue()-sh1.getRange('E30').getValue(),sh1.getRange('B30').getValue()-(sh1.getRange('E30').getValue()-2500)]);
};

This is probably easier to write:

function CopyCals() {
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName('Sheet2');
  var b30=sh1.getRange('B30').getValue();
  var e30=sh1.getRange('E30').getValue();
  sh2.appendRow([new Date(),'',b30,e30,b30-e30,b30-(E30-2500)]);
  //sh2.appendRow([new Date(),'',sh1.getRange('B30').getValue(),sh1.getRange('E30').getValue(),sh1.getRange('B30').getValue()-sh1.getRange('E30').getValue(),sh1.getRange('B30').getValue()-(sh1.getRange('E30').getValue()-2500)]);
}

I would probably prefer doing it something like this:

function CopyCals() {
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName('Sheet2');
  var vA=sh1.getRange('B30:B31').getValues();
  var b30=vA[0][0];
  var b31=vA[1][0];
  //var b30=sh1.getRange('B30').getValue();
  //var e30=sh1.getRange('E30').getValue();
  sh2.appendRow([new Date(),'',b30,e30,b30-e30,b30-(E30-2500)]);
  //sh2.appendRow([new Date(),'',sh1.getRange('B30').getValue(),sh1.getRange('E30').getValue(),sh1.getRange('B30').getValue()-sh1.getRange('E30').getValue(),sh1.getRange('B30').getValue()-(sh1.getRange('E30').getValue()-2500)]);
}

If you had a lot of data and it's all grouped together nicely then the later can save you lot's of time because you only have to do one read to get all of the data at one time.

In the latter approach its helpful to use your debugger to help you to get a picture of what the data looks like:

vA=[[value in B30],[value in B31]] so the vA[0][0] is B30 and vA[1][0] is B31

And here is yet another way to write the getRange() function:

function CopyCals() {
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName('Sheet2');
  var vA=sh1.getRange(30,2,2,1).getValues();
  //var vA=sh1.getRange('B30:B31').getValues();
  var b30=vA[0][0];
  var b31=vA[1][0];
  //var b30=sh1.getRange('B30').getValue();
  //var e30=sh1.getRange('E30').getValue();
  sh2.appendRow([new Date(),'',b30,e30,b30-e30,b30-(E30-2500)]);
  //sh2.appendRow([new Date(),'',sh1.getRange('B30').getValue(),sh1.getRange('E30').getValue(),sh1.getRange('B30').getValue()-sh1.getRange('E30').getValue(),sh1.getRange('B30').getValue()-(sh1.getRange('E30').getValue()-2500)]);
}

And just to make matters even a little more complicated for you a lot of times it's helpful to get the entire page of values all at one time so in that case this is what you left with.

function CopyCals() {
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName('Sheet2');
  var vA=sh1.getDataRange().getValues();
  var b30=vA[29][1];
  var b31=vA[30][1];
  //var vA=sh1.getRange('B30:B31').getValues();//gets all the data at one time
  //var b30=vA[0][0];
  //var b31=vA[1][0];
  //var b30=sh1.getRange('B30').getValue();
  //var e30=sh1.getRange('E30').getValue();
  sh2.appendRow([new Date(),'',b30,e30,b30-e30,b30-(E30-2500)]);
  //sh2.appendRow([new Date(),'',sh1.getRange('B30').getValue(),sh1.getRange('E30').getValue(),sh1.getRange('B30').getValue()-sh1.getRange('E30').getValue(),sh1.getRange('B30').getValue()-(sh1.getRange('E30').getValue()-2500)]);
}

All of these function do the same thing you can pick which one you want depending upon what your trying to accomplish.

Google Sheets appendRow with formula values

1 Answers

Getting the desired data