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

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

edited May 5 '18 at 19:28

TheMaster

10.4k3835

asked May 5 '18 at 18:48

Carl Manaster

33.1k1485135

add a comment |

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);

}

edited May 5 '18 at 19:28

TheMaster

10.4k3835

asked May 5 '18 at 18:48

Carl Manaster

33.1k1485135

add a comment |

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);

}

edited May 5 '18 at 19:28

TheMaster

10.4k3835

asked May 5 '18 at 18:48

Carl Manaster

33.1k1485135

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);

}

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

edited May 5 '18 at 19:28

TheMaster

10.4k3835

asked May 5 '18 at 18:48

Carl Manaster

33.1k1485135

edited May 5 '18 at 19:28

TheMaster

10.4k3835

asked May 5 '18 at 18:48

Carl Manaster

33.1k1485135

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

asked May 5 '18 at 18:48

Carl Manaster

33.1k1485135

asked May 5 '18 at 18:48

Carl Manaster

33.1k1485135

asked May 5 '18 at 18:48

Carl Manaster

33.1k1485135

add a comment |

2 Answers
2

active

oldest

votes

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().

answered May 5 '18 at 21:17

tehhowch

5,32641125

add a comment |

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.

edited Nov 22 '18 at 16:23

Zoe

12k74580

answered Nov 22 '18 at 10:50

Didier

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
});

}
});

draft saved

draft discarded

Sign up or log in

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

Post as a guest

Name

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

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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().

answered May 5 '18 at 21:17

tehhowch

5,32641125

add a comment |

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().

answered May 5 '18 at 21:17

tehhowch

5,32641125

add a comment |

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().

answered May 5 '18 at 21:17

tehhowch

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().

answered May 5 '18 at 21:17

tehhowch

5,32641125

answered May 5 '18 at 21:17

tehhowch

5,32641125

answered May 5 '18 at 21:17

tehhowch

5,32641125

answered May 5 '18 at 21:17

tehhowch

5,32641125

add a comment |

edited Nov 22 '18 at 16:23

Zoe

12k74580

answered Nov 22 '18 at 10:50

Didier

add a comment |

edited Nov 22 '18 at 16:23

Zoe

12k74580

answered Nov 22 '18 at 10:50

Didier

add a comment |

edited Nov 22 '18 at 16:23

Zoe

12k74580

answered Nov 22 '18 at 10:50

Didier

edited Nov 22 '18 at 16:23

Zoe

12k74580

answered Nov 22 '18 at 10:50

Didier

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

answered Nov 22 '18 at 10:50

Didier

answered Nov 22 '18 at 10:50

Didier

answered Nov 22 '18 at 10:50

Didier

add a comment |

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

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

Search This Blog

Ufyukyu