Table
How to copy sheet to another google sheet?
First, we import our source sheet and target sheet by ss_spreadsheet, ts_spreadsheet.
Then we can use copyTo function to copy the tab to another spredsheet tab.
|
1 2 3 4 |
var ss_spreadsheet = SpreadsheetApp.openById('sheetID_source_sheet') var ts_spreadsheet = SpreadsheetApp.openById('sheetID_target_sheet') var ss = ss_spreadsheet.getSheetByName("tab"); // ss = source sheet ss.copyTo(ts_spreadsheet).setName("tab"); // copy sheet to target spreadsheet |
How to copy one raw to target raw?
In this case, we also need import our source sheet and target sheet by ss_spreadsheet, ts_spreadsheet.
Second, we get the tab name by getSheetByName, and input your source range and target range to getRange.
At the end, if you want to copy the value then you can use the setValues and getValues function to copy the raw to target raw. If you want to copy the formulas then need to choose setFormulas and setFormulas function.
|
1 2 3 4 5 6 7 8 9 10 11 |
var ss_spreadsheet = SpreadsheetApp.openById('sheetID_source_sheet') var ts_spreadsheet = SpreadsheetApp.openById('sheetID_target_sheet') var ss = ss_spreadsheet.getSheetByName("tab"); // ss = source sheet var ts = ts_spreadsheet.getSheetByName("target"); // ts = target sheet // if you want copy the values ts.getRange("A1:D1").setValues(ss.getRange("A1:D1").getValues()); // if you want copy the formulas ts.getRange("A1:D1").setFormulas(ss.getRange("A1:D1").getFormulas()) |
How to copy the format to target range in google sheet?
After you import the source sheet and target sheet by ss_spreadsheet, ts_spreadsheet and set the range.
You can use below function to change the format:
- setFontStyles
- setFontWeights
- setFontSizes
- setFontLines
- setFontFamilies
- setBackgrounds
- setHorizontalAlignments
- setVerticalAlignments
|
1 2 3 4 5 6 7 8 9 10 11 12 |
var ss_spreadsheet = SpreadsheetApp.openById('sheetID_source_sheet') var ts_spreadsheet = SpreadsheetApp.openById('sheetID_target_sheet') var ss = ss_spreadsheet.getSheetByName("tab").getRange("A1:D1"); // ss = source sheet var ts = ts_spreadsheet.getSheetByName("target").getRange("A1:D1"); // ts = target sheet ts.setFontStyles(ss.getFontStyles()); ts.setFontWeights(ss.getFontWeights()); ts.setFontSizes(ss.getFontSizes()); ts.setFontLines(ss.getFontLines()); ts.setFontFamilies(ss.getFontFamilies()); ts.setBackgrounds(ss.getBackgrounds()); ts.setHorizontalAlignments(ss.getHorizontalAlignments()); ts.setVerticalAlignments(ss.getVerticalAlignments()); |
How to create raw DataValidation to target raw?
If you want to create DataValidation, first you need to use newDataValidation function to build the rule and use the setDataValidation functino to set the rule.
|
1 2 3 4 |
var ts_spreadsheet = SpreadsheetApp.openById('sheetID_target_sheet') var ts = ts_spreadsheet.getSheetByName("target").getRange("B1"); // ts = target sheet rule = SpreadsheetApp.newDataValidation().requireValueInList(["1","2"],true).build() ts.setDataValidation(rule); |
How to set the range name?
First, import the target sheet as ts_spreadsheet.
Then, you can use the setNamedRange, put the “string” and the range to be parameters as below.
|
1 2 3 |
var ts_spreadsheet = SpreadsheetApp.openById('sheetID_target_sheet') var ts = ts_spreadsheet.getSheetByName("target").getRange("B1"); // ts = target sheet ts_spreadsheet.setNamedRange("name_you_want",ts) |
Thanks for your reading. I hope this article will help you better understand how to copy the row or sheet in google app script.