Skip to content
Advertisement

Google Sheets, Import data from multiple sources into one, and create new tables

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!

example

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.| Authorization

Scripts 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:

enter image description here

References:

Advertisement