Why doesn't copyTo(… PASTE_VALUES) work in the middle of a macro?

1

One of my longstanding techniques with spreadsheets is Copy / Paste Special Values (C/PSV), in place. Having used formulas to produce the values I'm interested in, I C/PSV and can then delete the source data.

So I wrote a macro which uses this technique, but the cells wind up empty. But if I split the macro into two, ending the first macro before C/PSV, then everything works as intended. Why is this? Is there a better way to work around this problem? Here are my three macros.

function Step1() {

  var spreadsheet = SpreadsheetApp.getActive();

  var range = spreadsheet.getActiveRange();

  CopyRangeToNewSheet(spreadsheet, range);

  spreadsheet.getCurrentCell().offset(-1, 6).activate();

  FillInHeaders(spreadsheet);

  spreadsheet.getCurrentCell().offset(1, -4).activate();

  FillInFormulas(spreadsheet);

  spreadsheet.getCurrentCell().offset(0, -4, range.getNumRows(), 5).activate();

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

};



function Step2() {

  var spreadsheet = SpreadsheetApp.getActive();

  var keepers = spreadsheet.getRange('G:J');

  keepers.activate();

  keepers.copyTo(keepers, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  var discard = spreadsheet.getRange('A:F')

  discard.activate();

  spreadsheet.getActiveSheet().deleteColumns(discard.getColumn(), discard.getNumColumns());

};



function BothSteps() {

  var spreadsheet = SpreadsheetApp.getActive();

  var range = spreadsheet.getActiveRange();

  CopyRangeToNewSheet(spreadsheet, range);

  spreadsheet.getCurrentCell().offset(-1, 6).activate();

  FillInHeaders(spreadsheet);

  spreadsheet.getCurrentCell().offset(1, -4).activate();

  FillInFormulas(spreadsheet);

  spreadsheet.getCurrentCell().offset(0, -4, range.getNumRows(), 5).activate();

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  var keepers = spreadsheet.getRange('G:J');

  keepers.activate();

  keepers.copyTo(keepers, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  var discard = spreadsheet.getRange('A:F')

  discard.activate();

  spreadsheet.getActiveSheet().deleteColumns(discard.getColumn(), discard.getNumColumns());

};



function FillInHeaders(spreadsheet) {

  spreadsheet.getCurrentCell().setValue('First Name');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setValue('Last Name');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setValue('Middle Name');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setValue('Email');

}



function FillInFormulas(spreadsheet) {

  spreadsheet.getCurrentCell().setFormulaR1C1('=find(" ",R[0]C[-2])');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setFormulaR1C1('=if(iserr(R[0]C[-1]),R[0]C[-3],mid(R[0]C[-3],1,R[0]C[-1]))');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setFormulaR1C1('=if(iserr(R[0]C[-2]),"",mid(R[0]C[-4],R[0]C[-2]+1,50))');

  spreadsheet.getCurrentCell().offset(0, 2).activate();

  spreadsheet.getCurrentCell().setFormulaR1C1('=R[0]C[-5]');

}



function CopyRangeToNewSheet(spreadsheet, range) {

  var newSheet = spreadsheet.insertSheet(1);

  spreadsheet.setActiveSheet(newSheet, true);  

  spreadsheet.getCurrentCell().offset(1, 0).activate();

  range.copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

}

Here is the spreadsheet itself, with tabs Main, Result of Step1, Step2, and Result of Combined Steps:
https://docs.google.com/spreadsheets/d/1_nabq_mHuegz_eMIPPAlIgonv71Jh6OPi6qKzeNGGTI/edit?usp=sharing

google-apps-script google-sheets google-sheets-macros

share|improve this question

edited May 5 '18 at 19:28

TheMaster

10.4k3835

asked May 5 '18 at 18:48

Carl ManasterCarl Manaster

33.1k1485135

add a comment | 

1

One of my longstanding techniques with spreadsheets is Copy / Paste Special Values (C/PSV), in place. Having used formulas to produce the values I'm interested in, I C/PSV and can then delete the source data.

So I wrote a macro which uses this technique, but the cells wind up empty. But if I split the macro into two, ending the first macro before C/PSV, then everything works as intended. Why is this? Is there a better way to work around this problem? Here are my three macros.

function Step1() {

  var spreadsheet = SpreadsheetApp.getActive();

  var range = spreadsheet.getActiveRange();

  CopyRangeToNewSheet(spreadsheet, range);

  spreadsheet.getCurrentCell().offset(-1, 6).activate();

  FillInHeaders(spreadsheet);

  spreadsheet.getCurrentCell().offset(1, -4).activate();

  FillInFormulas(spreadsheet);

  spreadsheet.getCurrentCell().offset(0, -4, range.getNumRows(), 5).activate();

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

};



function Step2() {

  var spreadsheet = SpreadsheetApp.getActive();

  var keepers = spreadsheet.getRange('G:J');

  keepers.activate();

  keepers.copyTo(keepers, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  var discard = spreadsheet.getRange('A:F')

  discard.activate();

  spreadsheet.getActiveSheet().deleteColumns(discard.getColumn(), discard.getNumColumns());

};



function BothSteps() {

  var spreadsheet = SpreadsheetApp.getActive();

  var range = spreadsheet.getActiveRange();

  CopyRangeToNewSheet(spreadsheet, range);

  spreadsheet.getCurrentCell().offset(-1, 6).activate();

  FillInHeaders(spreadsheet);

  spreadsheet.getCurrentCell().offset(1, -4).activate();

  FillInFormulas(spreadsheet);

  spreadsheet.getCurrentCell().offset(0, -4, range.getNumRows(), 5).activate();

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  var keepers = spreadsheet.getRange('G:J');

  keepers.activate();

  keepers.copyTo(keepers, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  var discard = spreadsheet.getRange('A:F')

  discard.activate();

  spreadsheet.getActiveSheet().deleteColumns(discard.getColumn(), discard.getNumColumns());

};



function FillInHeaders(spreadsheet) {

  spreadsheet.getCurrentCell().setValue('First Name');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setValue('Last Name');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setValue('Middle Name');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setValue('Email');

}



function FillInFormulas(spreadsheet) {

  spreadsheet.getCurrentCell().setFormulaR1C1('=find(" ",R[0]C[-2])');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setFormulaR1C1('=if(iserr(R[0]C[-1]),R[0]C[-3],mid(R[0]C[-3],1,R[0]C[-1]))');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setFormulaR1C1('=if(iserr(R[0]C[-2]),"",mid(R[0]C[-4],R[0]C[-2]+1,50))');

  spreadsheet.getCurrentCell().offset(0, 2).activate();

  spreadsheet.getCurrentCell().setFormulaR1C1('=R[0]C[-5]');

}



function CopyRangeToNewSheet(spreadsheet, range) {

  var newSheet = spreadsheet.insertSheet(1);

  spreadsheet.setActiveSheet(newSheet, true);  

  spreadsheet.getCurrentCell().offset(1, 0).activate();

  range.copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

}

Here is the spreadsheet itself, with tabs Main, Result of Step1, Step2, and Result of Combined Steps:
https://docs.google.com/spreadsheets/d/1_nabq_mHuegz_eMIPPAlIgonv71Jh6OPi6qKzeNGGTI/edit?usp=sharing

google-apps-script google-sheets google-sheets-macros

share|improve this question

edited May 5 '18 at 19:28

TheMaster

10.4k3835

asked May 5 '18 at 18:48

Carl ManasterCarl Manaster

33.1k1485135

add a comment | 

1

1

1

1

One of my longstanding techniques with spreadsheets is Copy / Paste Special Values (C/PSV), in place. Having used formulas to produce the values I'm interested in, I C/PSV and can then delete the source data.

So I wrote a macro which uses this technique, but the cells wind up empty. But if I split the macro into two, ending the first macro before C/PSV, then everything works as intended. Why is this? Is there a better way to work around this problem? Here are my three macros.

function Step1() {

  var spreadsheet = SpreadsheetApp.getActive();

  var range = spreadsheet.getActiveRange();

  CopyRangeToNewSheet(spreadsheet, range);

  spreadsheet.getCurrentCell().offset(-1, 6).activate();

  FillInHeaders(spreadsheet);

  spreadsheet.getCurrentCell().offset(1, -4).activate();

  FillInFormulas(spreadsheet);

  spreadsheet.getCurrentCell().offset(0, -4, range.getNumRows(), 5).activate();

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

};



function Step2() {

  var spreadsheet = SpreadsheetApp.getActive();

  var keepers = spreadsheet.getRange('G:J');

  keepers.activate();

  keepers.copyTo(keepers, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  var discard = spreadsheet.getRange('A:F')

  discard.activate();

  spreadsheet.getActiveSheet().deleteColumns(discard.getColumn(), discard.getNumColumns());

};



function BothSteps() {

  var spreadsheet = SpreadsheetApp.getActive();

  var range = spreadsheet.getActiveRange();

  CopyRangeToNewSheet(spreadsheet, range);

  spreadsheet.getCurrentCell().offset(-1, 6).activate();

  FillInHeaders(spreadsheet);

  spreadsheet.getCurrentCell().offset(1, -4).activate();

  FillInFormulas(spreadsheet);

  spreadsheet.getCurrentCell().offset(0, -4, range.getNumRows(), 5).activate();

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  var keepers = spreadsheet.getRange('G:J');

  keepers.activate();

  keepers.copyTo(keepers, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  var discard = spreadsheet.getRange('A:F')

  discard.activate();

  spreadsheet.getActiveSheet().deleteColumns(discard.getColumn(), discard.getNumColumns());

};



function FillInHeaders(spreadsheet) {

  spreadsheet.getCurrentCell().setValue('First Name');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setValue('Last Name');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setValue('Middle Name');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setValue('Email');

}



function FillInFormulas(spreadsheet) {

  spreadsheet.getCurrentCell().setFormulaR1C1('=find(" ",R[0]C[-2])');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setFormulaR1C1('=if(iserr(R[0]C[-1]),R[0]C[-3],mid(R[0]C[-3],1,R[0]C[-1]))');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setFormulaR1C1('=if(iserr(R[0]C[-2]),"",mid(R[0]C[-4],R[0]C[-2]+1,50))');

  spreadsheet.getCurrentCell().offset(0, 2).activate();

  spreadsheet.getCurrentCell().setFormulaR1C1('=R[0]C[-5]');

}



function CopyRangeToNewSheet(spreadsheet, range) {

  var newSheet = spreadsheet.insertSheet(1);

  spreadsheet.setActiveSheet(newSheet, true);  

  spreadsheet.getCurrentCell().offset(1, 0).activate();

  range.copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

}

Here is the spreadsheet itself, with tabs Main, Result of Step1, Step2, and Result of Combined Steps:
https://docs.google.com/spreadsheets/d/1_nabq_mHuegz_eMIPPAlIgonv71Jh6OPi6qKzeNGGTI/edit?usp=sharing

google-apps-script google-sheets google-sheets-macros

share|improve this question

edited May 5 '18 at 19:28

TheMaster

10.4k3835

asked May 5 '18 at 18:48

Carl ManasterCarl Manaster

33.1k1485135

One of my longstanding techniques with spreadsheets is Copy / Paste Special Values (C/PSV), in place. Having used formulas to produce the values I'm interested in, I C/PSV and can then delete the source data.

So I wrote a macro which uses this technique, but the cells wind up empty. But if I split the macro into two, ending the first macro before C/PSV, then everything works as intended. Why is this? Is there a better way to work around this problem? Here are my three macros.

function Step1() {

  var spreadsheet = SpreadsheetApp.getActive();

  var range = spreadsheet.getActiveRange();

  CopyRangeToNewSheet(spreadsheet, range);

  spreadsheet.getCurrentCell().offset(-1, 6).activate();

  FillInHeaders(spreadsheet);

  spreadsheet.getCurrentCell().offset(1, -4).activate();

  FillInFormulas(spreadsheet);

  spreadsheet.getCurrentCell().offset(0, -4, range.getNumRows(), 5).activate();

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

};



function Step2() {

  var spreadsheet = SpreadsheetApp.getActive();

  var keepers = spreadsheet.getRange('G:J');

  keepers.activate();

  keepers.copyTo(keepers, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  var discard = spreadsheet.getRange('A:F')

  discard.activate();

  spreadsheet.getActiveSheet().deleteColumns(discard.getColumn(), discard.getNumColumns());

};



function BothSteps() {

  var spreadsheet = SpreadsheetApp.getActive();

  var range = spreadsheet.getActiveRange();

  CopyRangeToNewSheet(spreadsheet, range);

  spreadsheet.getCurrentCell().offset(-1, 6).activate();

  FillInHeaders(spreadsheet);

  spreadsheet.getCurrentCell().offset(1, -4).activate();

  FillInFormulas(spreadsheet);

  spreadsheet.getCurrentCell().offset(0, -4, range.getNumRows(), 5).activate();

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  var keepers = spreadsheet.getRange('G:J');

  keepers.activate();

  keepers.copyTo(keepers, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  var discard = spreadsheet.getRange('A:F')

  discard.activate();

  spreadsheet.getActiveSheet().deleteColumns(discard.getColumn(), discard.getNumColumns());

};



function FillInHeaders(spreadsheet) {

  spreadsheet.getCurrentCell().setValue('First Name');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setValue('Last Name');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setValue('Middle Name');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setValue('Email');

}



function FillInFormulas(spreadsheet) {

  spreadsheet.getCurrentCell().setFormulaR1C1('=find(" ",R[0]C[-2])');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setFormulaR1C1('=if(iserr(R[0]C[-1]),R[0]C[-3],mid(R[0]C[-3],1,R[0]C[-1]))');

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getCurrentCell().setFormulaR1C1('=if(iserr(R[0]C[-2]),"",mid(R[0]C[-4],R[0]C[-2]+1,50))');

  spreadsheet.getCurrentCell().offset(0, 2).activate();

  spreadsheet.getCurrentCell().setFormulaR1C1('=R[0]C[-5]');

}



function CopyRangeToNewSheet(spreadsheet, range) {

  var newSheet = spreadsheet.insertSheet(1);

  spreadsheet.setActiveSheet(newSheet, true);  

  spreadsheet.getCurrentCell().offset(1, 0).activate();

  range.copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

}

Here is the spreadsheet itself, with tabs Main, Result of Step1, Step2, and Result of Combined Steps:
https://docs.google.com/spreadsheets/d/1_nabq_mHuegz_eMIPPAlIgonv71Jh6OPi6qKzeNGGTI/edit?usp=sharing

google-apps-script google-sheets google-sheets-macros

google-apps-script google-sheets google-sheets-macros

share|improve this question

edited May 5 '18 at 19:28

TheMaster

10.4k3835

asked May 5 '18 at 18:48

Carl ManasterCarl Manaster

33.1k1485135

share|improve this question

edited May 5 '18 at 19:28

TheMaster

10.4k3835

asked May 5 '18 at 18:48

Carl ManasterCarl Manaster

33.1k1485135

share|improve this question

share|improve this question

edited May 5 '18 at 19:28

TheMaster

10.4k3835

edited May 5 '18 at 19:28

TheMaster

10.4k3835

edited May 5 '18 at 19:28

TheMaster

10.4k3835

10.4k3835

asked May 5 '18 at 18:48

Carl ManasterCarl Manaster

33.1k1485135

asked May 5 '18 at 18:48

Carl ManasterCarl Manaster

33.1k1485135

asked May 5 '18 at 18:48

Carl ManasterCarl Manaster

33.1k1485135

33.1k1485135

add a comment | 

add a comment | 

2 Answers
2

active

oldest

votes

2

SpreadsheetApp.flush() is likely the missing step in your macro. Basically, Apps Script optimizes reads & writes internally, and if you don't call this method, it is free to do things its way.

Adding this where you currently separate your task into "Macro 1" and "Macro 2" should resolve the issue:

...

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  // Force formulas to calculate and pending writes to be written.

  SpreadsheetApp.flush();

  // Read formula results and save as values.

  var keepers = spreadsheet.getRange('G:J');

...

An additional method would be to condense your scripts from the "transactional" approach of a recorded macro, to the batch / efficient "big picture" view, by using setValues() instead of copyTo:

...

  SpreadsheetApp.flush();

  var toKeep = spreadsheet.getRange('G:J');

  toKeep.setValues(toKeep.getValues());

  toKeep.getSheet().deleteColumns(1, toKeep.getColumn() - 1);

}

Note that you still want the call to .flush().

share|improve this answer

answered May 5 '18 at 21:17

tehhowchtehhowch

5,32641125

add a comment | 

0

Confronted to a similar issue (e.g. copying the result of a calculation from one range of cells into another range in which I needed the result alone without the formulas), using {contentsOnly:true} instead of SpreadsheetApp.CopyPasteType.PASTE_VALUES did the job.

share|improve this answer

edited Nov 22 '18 at 16:23

Zoe

12k74580

answered Nov 22 '18 at 10:50

DidierDidier

1

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f50192942%2fwhy-doesnt-copyto-paste-values-work-in-the-middle-of-a-macro%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

active

oldest

votes

active

oldest

votes

2

SpreadsheetApp.flush() is likely the missing step in your macro. Basically, Apps Script optimizes reads & writes internally, and if you don't call this method, it is free to do things its way.

Adding this where you currently separate your task into "Macro 1" and "Macro 2" should resolve the issue:

...

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  // Force formulas to calculate and pending writes to be written.

  SpreadsheetApp.flush();

  // Read formula results and save as values.

  var keepers = spreadsheet.getRange('G:J');

...

An additional method would be to condense your scripts from the "transactional" approach of a recorded macro, to the batch / efficient "big picture" view, by using setValues() instead of copyTo:

...

  SpreadsheetApp.flush();

  var toKeep = spreadsheet.getRange('G:J');

  toKeep.setValues(toKeep.getValues());

  toKeep.getSheet().deleteColumns(1, toKeep.getColumn() - 1);

}

Note that you still want the call to .flush().

share|improve this answer

answered May 5 '18 at 21:17

tehhowchtehhowch

5,32641125

add a comment | 

2

SpreadsheetApp.flush() is likely the missing step in your macro. Basically, Apps Script optimizes reads & writes internally, and if you don't call this method, it is free to do things its way.

Adding this where you currently separate your task into "Macro 1" and "Macro 2" should resolve the issue:

...

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  // Force formulas to calculate and pending writes to be written.

  SpreadsheetApp.flush();

  // Read formula results and save as values.

  var keepers = spreadsheet.getRange('G:J');

...

An additional method would be to condense your scripts from the "transactional" approach of a recorded macro, to the batch / efficient "big picture" view, by using setValues() instead of copyTo:

...

  SpreadsheetApp.flush();

  var toKeep = spreadsheet.getRange('G:J');

  toKeep.setValues(toKeep.getValues());

  toKeep.getSheet().deleteColumns(1, toKeep.getColumn() - 1);

}

Note that you still want the call to .flush().

share|improve this answer

answered May 5 '18 at 21:17

tehhowchtehhowch

5,32641125

add a comment | 

2

2

2

SpreadsheetApp.flush() is likely the missing step in your macro. Basically, Apps Script optimizes reads & writes internally, and if you don't call this method, it is free to do things its way.

Adding this where you currently separate your task into "Macro 1" and "Macro 2" should resolve the issue:

...

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  // Force formulas to calculate and pending writes to be written.

  SpreadsheetApp.flush();

  // Read formula results and save as values.

  var keepers = spreadsheet.getRange('G:J');

...

An additional method would be to condense your scripts from the "transactional" approach of a recorded macro, to the batch / efficient "big picture" view, by using setValues() instead of copyTo:

...

  SpreadsheetApp.flush();

  var toKeep = spreadsheet.getRange('G:J');

  toKeep.setValues(toKeep.getValues());

  toKeep.getSheet().deleteColumns(1, toKeep.getColumn() - 1);

}

Note that you still want the call to .flush().

share|improve this answer

answered May 5 '18 at 21:17

tehhowchtehhowch

5,32641125

SpreadsheetApp.flush() is likely the missing step in your macro. Basically, Apps Script optimizes reads & writes internally, and if you don't call this method, it is free to do things its way.

Adding this where you currently separate your task into "Macro 1" and "Macro 2" should resolve the issue:

...

  spreadsheet.getCurrentCell().offset(0, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  // Force formulas to calculate and pending writes to be written.

  SpreadsheetApp.flush();

  // Read formula results and save as values.

  var keepers = spreadsheet.getRange('G:J');

...

An additional method would be to condense your scripts from the "transactional" approach of a recorded macro, to the batch / efficient "big picture" view, by using setValues() instead of copyTo:

...

  SpreadsheetApp.flush();

  var toKeep = spreadsheet.getRange('G:J');

  toKeep.setValues(toKeep.getValues());

  toKeep.getSheet().deleteColumns(1, toKeep.getColumn() - 1);

}

Note that you still want the call to .flush().

share|improve this answer

answered May 5 '18 at 21:17

tehhowchtehhowch

5,32641125

share|improve this answer

share|improve this answer

answered May 5 '18 at 21:17

tehhowchtehhowch

5,32641125

answered May 5 '18 at 21:17

tehhowchtehhowch

5,32641125

answered May 5 '18 at 21:17

tehhowchtehhowch

5,32641125

5,32641125

add a comment | 

add a comment | 

0

Confronted to a similar issue (e.g. copying the result of a calculation from one range of cells into another range in which I needed the result alone without the formulas), using {contentsOnly:true} instead of SpreadsheetApp.CopyPasteType.PASTE_VALUES did the job.

share|improve this answer

edited Nov 22 '18 at 16:23

Zoe

12k74580

answered Nov 22 '18 at 10:50

DidierDidier

1

add a comment | 

0

Confronted to a similar issue (e.g. copying the result of a calculation from one range of cells into another range in which I needed the result alone without the formulas), using {contentsOnly:true} instead of SpreadsheetApp.CopyPasteType.PASTE_VALUES did the job.

share|improve this answer

edited Nov 22 '18 at 16:23

Zoe

12k74580

answered Nov 22 '18 at 10:50

DidierDidier

1

add a comment | 

0

0

0

Confronted to a similar issue (e.g. copying the result of a calculation from one range of cells into another range in which I needed the result alone without the formulas), using {contentsOnly:true} instead of SpreadsheetApp.CopyPasteType.PASTE_VALUES did the job.

share|improve this answer

edited Nov 22 '18 at 16:23

Zoe

12k74580

answered Nov 22 '18 at 10:50

DidierDidier

1

Confronted to a similar issue (e.g. copying the result of a calculation from one range of cells into another range in which I needed the result alone without the formulas), using {contentsOnly:true} instead of SpreadsheetApp.CopyPasteType.PASTE_VALUES did the job.

share|improve this answer

edited Nov 22 '18 at 16:23

Zoe

12k74580

answered Nov 22 '18 at 10:50

DidierDidier

1

share|improve this answer

share|improve this answer

edited Nov 22 '18 at 16:23

Zoe

12k74580

edited Nov 22 '18 at 16:23

Zoe

12k74580

edited Nov 22 '18 at 16:23

Zoe

12k74580

12k74580

answered Nov 22 '18 at 10:50

DidierDidier

1

answered Nov 22 '18 at 10:50

DidierDidier

1

answered Nov 22 '18 at 10:50

DidierDidier

1

1

add a comment | 

add a comment | 

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f50192942%2fwhy-doesnt-copyto-paste-values-work-in-the-middle-of-a-macro%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here