Help With copyTo class Google Sheets

419 views
Skip to first unread message

fernando....@dish.com

unread,
Oct 13, 2022, 6:30:12 PM10/13/22
to Google Apps Script Community
Hello everyone,

Here is the code that I am trying to execute:

function copyPasteEmp() {
let copyRange = sheet.getRange(2,1,lastRow-1);
  copyRange.setValue('=IF(B2="",A1,B2)');
  copyRange.copyTo(sheet.getRange("B2"), {contentsOnly: true});
}

It successfully set the formula where I want, but the copyTo portion leaves me with a blank B2 column. If I leave out {contentsOnly: true} the contents do copy over to column B2, but I need to have only the values copied over, not the formula. 

I'm failing to understand what is being done incorrectly.

Tanaike

unread,
Oct 14, 2022, 1:31:11 AM10/14/22
to Google Apps Script Community
Unfortunately, although I'm not sure whether I could correctly understand your expected goal, in your script, how about using `SpreadsheetApp.flush()` as follows?

From


copyRange.setValue('=IF(B2="",A1,B2)');
copyRange.copyTo(sheet.getRange("B2"), {contentsOnly: true});

To

copyRange.setValue('=IF(B2="",A1,B2)');
SpreadsheetApp.flush(); // Added

Fernando DosSantos

unread,
Oct 15, 2022, 2:13:42 PM10/15/22
to google-apps-sc...@googlegroups.com
Thank you for replying and for your help. Yes, adding SpreadsheetApp.flush() helped to correct the issue. Before I added that line of code, when executing the function, it did appear to just be going 'too fast'. Adding .flush() seems to 'pause' the process long enough to then finish the function correctly. 

I never would have hit on using this bit of code without your help.

Fernando

On Sat, Oct 15, 2022 at 9:48 AM Tanaike <kanshi...@gmail.com> wrote:

 This message originated outside of DISH and was sent by: google-apps-sc...@googlegroups.com



--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/HBQlGPAYoVk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1eda7935-4578-4249-bde5-900cb3be7d5an%40googlegroups.com.

Tanaike

unread,
Oct 15, 2022, 9:27:19 PM10/15/22
to Google Apps Script Community
Thank you for replying. I'm glad your issue was resolved. As additional information, the official document of `flush()` is https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#flush
Reply all
Reply to author
Forward
0 new messages