CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
ck88
ck88

Reputation: 23

Google script Copy value of formula result

I have to copy three cells from one sheet to another in order to process the formula on that sheet. I then need to copy the formula output onto the first sheet. I have the copy working for the 'input cells' but when I use the same line of code for the formula cell it doesn't take the correct inputs values. I thought I need to select a cell first to kick through the values but it doesn't work. This is the original piece of code.

var spreadsheet = SpreadsheetApp.getActive();

//copy values for formula cells
spreadsheet.getSheetByName('Sheet6').getRange(2,3).copyTo(spreadsheet.getSheetByName('student').getRange(1,1).activate(), {contentsOnly:true}); 
spreadsheet.getSheetByName('Sheet6').getRange(2,4).copyTo(spreadsheet.getSheetByName('student').getRange(2,1).activate(), {contentsOnly:true});    
spreadsheet.getSheetByName('Sheet6').getRange(2,5).copyTo(spreadsheet.getSheetByName('student').getRange(3,1).activate(), {contentsOnly:true});

//copy formula output to sheet
spreadsheet.getSheetByName('student').getRange(2,22).copyTo(spreadsheet.getSheetByName('Sheet6').getRange(2,6).activate(), {contentsOnly:true});

Thanks

Upvotes: 0

Views: 7830

Answers (1)

JSDBroughton
JSDBroughton

Reputation: 4034

All changes to cells are buffered until the end of the script processing. As such you will want to apply the .flush() method to the student spreadsheet.

var studentSheet = spreadsheet.getSheetByName('student');
var sourceSheet = spreadsheet.getSheetByName('Sheet6');

// copyTo rows
sourceSheet.getRange(2, 5)
    .copyTo(studentSheet.getRange(3, 1), {contentsOnly: true});
...

SpreadsheetApp.flush(); // applies all values to the student spreadsheet
                        // and therefore all the formula cells update

// get value from calculated formula cell
studentSheet.getRange(2, 22)
    .copyTo(sourceSheet.getRange(2, 6), {contentsOnly: true});

Documentation for flush().

Upvotes: 6

Related Questions