Copying Cells, Format, etc. - Episode 1.6 | Apps Script ~ Spreadsheet Service

[@fabriciosouza408]

Man, you're just helping me a lot! I'm from Brazil and I'm learning appscript and you're courses are really great. I hope you keep doing this. Thank you so much for this!

[@myanhdo1562]

thank you so much, i have been looking for this for so longgggg. take care sir

[@rameshjangir7393]

Thanks bro, your script works with little bit of modification as per my requirements, thumbs up.

[@fredguedespereira4458]

Thank's for the videos!

[@DavidWeissProgramming]

Glad you like them!

[@RajSINGH-si5gt]

really appreciate your work. thank you so much for such informative videos.

[@sergejlust7927]

great topic, thx. Exactly what I needed.

[@TheMoOnS38]

Hi David, thank you so mutch for this video ! Do you think it’s possible to use getActiveRangeList() and copyTo ?

[@asimali5820]

Is there any way to prevent user copy paste in Googlesheet cells??

[@davidsebastian9651]

Hi David, Is there a way to get this work now that copyTo is not supported?

[@DavidWeissProgramming]

Hi David, nice name! :) copyTo() is still a supported method in SpreadsheetApp and this code should still run. Is it not working for you? Make sure there are no typos in your own code and you have replaced the value of the "id" variable with the id of your own spreadsheet. If you need more help, feel free to reply with the code you're using along with any errors you may be receiving in the console.

[@edu_aqui]

Pity it doesn't work copying from one spreadsheet to another. 🙁
It only works between sheets of the same spreadsheet.

[@DavidWeissProgramming]

Hi Eduardo! Yes, agreed, unfortunately you cannot use copyTo to copy something from one spreadsheet to another :( However, there are a lot of workarounds for accomplishing this same task! Two I can think of right now is: 1) You can use copyTo() to copy the entire sheet over to your destination spreadsheet, and then once it's there, use the copyTo() method again to copy just the range you wanted to your destination range. Then, you can delete the copied Sheet once you are done. 2) You can use methods like getValues(), getBackground(), getFormula(), getFontColor(), getFontFamily(), getFontSize(), etc. to copy the data and formatting and then use setValues(), setBackground(), setFormula(), setFontColor(), etc. to send it to your destination spreadsheet. Example code is shown below.
Option 1:
function copyCellAcrossSpreadsheets() {
const sourceSpreadsheet = SpreadsheetApp.openById("1BmMuBDWVx0i2TLxVik7rDy5yL5dvwceclZnR679xoaU");
const destinationSpreadsheet = SpreadsheetApp.openById("1qAIEhpYHfVLQ1BwOGb4vBe8QJGyeEkcREZxsEJnINaI");
// Copy entire sheet over
const tempSheet = sourceSpreadsheet.getSheetByName("Sheet1").copyTo(destinationSpreadsheet);
// Change these numbers around to match the range you're trying to copying
const tempRange = tempSheet.getRange(15, 7);
const destinationRange = destinationSpreadsheet.getSheetByName("Sheet1").getRange(17, 6);
// Copy over contents between sheets
tempRange.copyTo(destinationRange);
// Clean up, delete temp sheet
destinationSpreadsheet.deleteSheet(tempSheet);
}
Option 2:
function copyCellAcrossSpreadsheetsV2() {
const sourceSpreadsheet = SpreadsheetApp.openById("1BmMuBDWVx0i2TLxVik7rDy5yL5dvwceclZnR679xoaU");
const destinationSpreadsheet = SpreadsheetApp.openById("1qAIEhpYHfVLQ1BwOGb4vBe8QJGyeEkcREZxsEJnINaI");
// Define source and destination ranges
const sourceRange = sourceSpreadsheet.getSheetByName("Sheet1").getRange(15, 7);
const destinationRange = destinationSpreadsheet.getSheetByName("Sheet1").getRange(9, 6);
// Copy source values and formatting
const sourceValues = sourceRange.getValues();
const sourceBackgrounds = sourceRange.getBackgrounds();
const sourceFontColors = sourceRange.getFontColors();
const sourceFontFamilies = sourceRange.getFontFamilies();
const sourceFontStyles = sourceRange.getFontStyles();
const sourceFontSizes = sourceRange.getFontSizes();
// Feel free to get whatever more styles you need...
// Copy this to destination range
destinationRange.setValues(sourceValues);
destinationRange.setBackgrounds(sourceBackgrounds);
destinationRange.setFontColors(sourceFontColors);
destinationRange.setFontFamilies(sourceFontFamilies);
destinationRange.setFontStyles(sourceFontStyles);
destinationRange.setFontSizes(sourceFontSizes);
}
Hopefully this helps! Let me know if you have any questions on the above code.