The source data that i want to import, its on a different multiple worksheets This image contains all the data that i want to send to the main table, Two of the tables are filled in manually, i want to do the third one using a script entirely
I’ve already figured out how to write data from one sheet to another using this code:
function getdata() { var files = DriveApp.getFolderById("folder id").getFiles() while (files.hasNext()) { var file = files.next(); var shoot = SpreadsheetApp.openById(file.getId()); var sourcesheet = SpreadsheetApp.getActive().getSheetByName("Sheet name");; var sourcerange = sourcesheet.getRange('A:A'); var sourcevalues = sourcerange.getValues(); var destsheet = shoot.getSheetByName('Sheet name'); var destrange = destsheet.getRange('B:B'); destrange.setValues(sourcevalues); } }
Issue is with this is that it doesn’t create new rows for new data, and it doesnt take into account when a new sheet is created. I’m a lil confused, im new to javascript and im just the IT guy, but im willing to learn!
Advertisement
Answer
Answer:
Looks like you want to use the copyTo()
method of the Range
class.
More Information:
As per the documentation:
copyTo(destination)
Copies the data from a range of cells to another range of cells. Both the values and formatting are copied.
// The code below copies the first 5 columns over to the 6th column. var sheet = SpreadsheetApp.getActiveSheet(); var rangeToCopy = sheet.getRange(1, 1, sheet.getMaxRows(), 5); rangeToCopy.copyTo(sheet.getRange(1, 6));Parameters | Name | Type | Description | |——|——|————-| |
destination
|Range
| A destination range to copy to; only the top-left cell position is relevant.| AuthorizationScripts that use this method require authorization with one or more of the following scopes:
https://www.googleapis.com/auth/spreadsheets.currentonly
https://www.googleapis.com/auth/spreadsheets
Working Example:
Using a script such as the following:
function myFunction() { SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("C6:D20").copyTo( SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange("E6:F20") ) }
You can copy data from one range to another while preserving properties such as text decoration: