logo
down
shadow

How to copy multiple range to the exact same location in another sheet using google script?


How to copy multiple range to the exact same location in another sheet using google script?

Content Index :

How to copy multiple range to the exact same location in another sheet using google script?
Tag : google-apps-script , By : janik
Date : January 12 2021, 09:11 PM

seems to work fine Since a RangeList is a collection of ranges you have to loop through the collection and copy each range.
function test () {
  try {
    var spread = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spread.getSheetByName("Sheet1");
    var rlist = sheet.getRangeList(["A1:A5", "B7:B10", "C12:C16"]);
    sheet = spread.getSheetByName("Sheet2");
    for( var i=0; i<rlist.getRanges().length; i++ ) {
      var r1 = rlist.getRanges()[i];
      var r2 = sheet.getRange(r1.getA1Notation());
      r1.copyTo(r2);
    }
  }
  catch(err) {
    Logger.log(err);
  }
}

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Google script: How to move or copy multiple rows to a target sheet


Tag : google-apps-script , By : KL.
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I am trying to get the source rows that match my conditions to move to a target sheet. Then delete the source rows in the source sheet. Please see below code. Any help would be much appreciated. , This is a general code for moving range to range
function moveRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sourceSheet = ss.getSheets()[0];  
  var sourceRange = sourceSheet.getDataRange();
  var targetSheet = ss.getSheets()[1];  
  var targetRange = targetSheet.getRange(1, 1, sourceSheet.getLastRow(), sourceSheet.getLastColumn());

  sourceRange.moveTo(targetRange) ;

}

Copy selected range in a sheet to other place in a same sheet with Google Scripteditor


Tag : google-apps-script , By : Roel van Dijk
Date : March 29 2020, 07:55 AM
To fix the issue you can do The reason of error in your script is that getSelection() is not directly returned the range object. Please use getActiveRange() for getSelection(). So can you try this modification?
From :
var selection = sheet.getSelection();
var selection = sheet.getSelection().getActiveRange();

Google Script: How to copy and paste range to a new sheet on a row that is not empty


Tag : javascript , By : Chris Tattum
Date : March 29 2020, 07:55 AM
I wish this helpful for you Instead of using .getLastRow() try calling a function that computes the last cell in a column and see if that works. Example:
function transferArchive() {

//TAB 1
//Target Range - Tab 1, Range 1
var sss = SpreadsheetApp.getActiveSpreadsheet();
var ss = sss.getSheetByName('Front Sheet');
var range = ss.getRange('B4:E');
var data = range.getValues();

//Destination Range - Tab 1, Range 1
var tss = SpreadsheetApp.getActiveSpreadsheet();
var ts = tss.getSheetByName('Printed POs');
ts.getRange(lastRow(ts, 1), 2, data.length, data[0].length).setValues(data);
}

function lastRow(sheet, colNum) {
var v = sheet.getRange(1, colNum, sheet.getLastRow()).getValues(),
    l = v.length,
    r;
while (l > 0) {
    if (v[l] && v[l][0].toString().length > 0) {
        r = (l + 2);
        break;
    } else {
        l--;
    }
}
return r ? r : 1;

}

Copy and paste data from one sheet to multiple where range matches sheet names


Tag : excel , By : Chris Woods
Date : March 29 2020, 07:55 AM
it helps some times Good morning all,
David, thanks very much for your help with this. I really didn't want you to think I was trying to get someone to give me the answer and I had tried a few other things before asking the question, but I neglected to show any evidence of my workings. Rookie mistake and I apologise for this.
    Option Explicit

Dim RawDataCol As String
Dim ListCol As String
Dim AdvRng As String
Dim RawDataRng As String
Dim SiteAbrRng As String
Dim ShiftCols As String
Private Sub SetParameters()

'Cell Address where RawData is pasted to each of the site sheets
    RawDataCol = "A2"

'Column where the Unique List is cleared and pasted
    ListCol = "L"

'Advanced Filter Range
    AdvRng = "A1:K2"

'Pasted Raw Data Columns on each sheet
    RawDataRng = "A2:K"

'Site Abr gets pasted to the address during loop
    SiteAbrRng = "A2"

'Range that gets deleted after pasting Raw Data to each sheet
    ShiftCols = "A2:K2"

End Sub
Sub CopyDataToSheets()

On Error GoTo ErrorHandler

AppSettings (True)

Dim StartTime As Double
Dim SecondsElapsed As Double

    StartTime = Timer

Dim wbk As Workbook
Dim sht_RawData As Worksheet, sht_target As Worksheet, sht_AdvancedFilter As Worksheet, sht_TurbineData As Worksheet
Dim tbl_RawData As ListObject
Dim LastRow1 As Long, LastRow2 As Long, UniqueListCount As Long
Dim MyArr As Variant
Dim ArrTest As Boolean
Dim x As Long, AdvRowNo As Long

Set wbk = ThisWorkbook

SetParameters

Set sht_RawData = wbk.Worksheets("Raw Data")
Set sht_AdvancedFilter = wbk.Worksheets("Advanced Filter")
Set sht_TurbineData = wbk.Worksheets("Turbine Data")

Set tbl_RawData = sht_RawData.ListObjects("_00")

'clear unqie list of SiteAbr
With sht_TurbineData

    LastRow1 = .Cells(Rows.Count, 12).End(xlUp).Row

    If LastRow1 > 1 Then
        'sht_TurbineData.Range("L1:L" & LastRow1).ClearContents
        sht_TurbineData.Range(ListCol & 1 & ":" & ListCol & LastRow1).ClearContents
    End If

End With

'Copy Unqiue list of SiteAbr to Turbie Data Sheet
tbl_RawData.Range.Columns(1).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=sht_TurbineData.Range(ListCol & 1), _
    Unique:=True

    LastRow1 = sht_TurbineData.Cells(Rows.Count, sht_TurbineData.Range(ListCol & 1).Column).End(xlUp).Row

    'Sort Unique List
    sht_TurbineData.Range("L1:L" & LastRow1).Sort _
    Key1:=sht_TurbineData.Range("L1"), _
    Order1:=xlAscending, _
    Header:=xlYes

'Load unique site Abr to array
With sht_TurbineData

    'MyArr = Application.Transpose(.Range("L2:L" & LastRow1))
    MyArr = Application.Transpose(.Range(ListCol & 2 & ":" & ListCol & LastRow1))

    UniqueListCount = LastRow1 - 1

End With

'Test Array conditions for 0 items or 1 item

ArrTest = IsArray(MyArr)

If UniqueListCount = 1 Then
    MyArr = Array(MyArr)

ElseIf UniqueListCount = 0 Then
    GoTo ExitSub

End If

    For x = LBound(MyArr) To UBound(MyArr)

        Set sht_target = wbk.Worksheets(MyArr(x))

                With sht_target

                    'Find the last non blank row of the target paste sheet
                    LastRow2 = .Cells(Rows.Count, 1).End(xlUp).Row

                    'Clear contents if the Last Row is not the header row
                    If LastRow2 > 1 Then
                        .Range(RawDataRng & LastRow2).ClearContents
                    End If

                    sht_AdvancedFilter.Range(SiteAbrRng) = MyArr(x)

                    'Filter Source Data and Copy to Target Sheet
                    tbl_RawData.Range.AdvancedFilter _
                        Action:=xlFilterCopy, _
                        CriteriaRange:=sht_AdvancedFilter.Range(AdvRng), _
                        CopyToRange:=.Range(RawDataCol), _
                        Unique:=False

                    'Remove the first row as this contains the headers
                    .Range(ShiftCols).Delete xlShiftUp

                End With

    Next x

ExitSub:
    SecondsElapsed = Round(Timer - StartTime, 3)

    AppSettings (False)

    'Notify user in seconds
    MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation



Exit Sub
ErrorHandler:
    MsgBox (Err.Number & vbNewLine & Err.Description)
    GoTo ExitSub

End Sub
Sub ClearAllSheets()

Dim tbl_SiteList As ListObject
Dim wbk As Workbook
Dim sht_target As Worksheet, sht_TurbineData As Worksheet
Dim MyArray As Variant
Dim x As Long, LastRow As Long

Set wbk = ThisWorkbook
Set sht_TurbineData = wbk.Worksheets("Turbine Data")
Set tbl_SiteList = sht_TurbineData.ListObjects("SiteList")

SetParameters

MyArray = Application.Transpose(tbl_SiteList.DataBodyRange)

For x = LBound(MyArray) To UBound(MyArray)

    Set sht_target = wbk.Worksheets(MyArray(x))

        LastRow = sht_target.Cells(Rows.Count, 1).End(xlUp).Row

        If LastRow > 1 Then
            sht_target.Range("A2:K" & LastRow).ClearContents
        End If

Next x

End Sub
Private Sub AppSettings(Opt As Boolean)

If Opt = True Then

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

ElseIf Opt = False Then

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End If

End Sub

Copy/Push data from array into a sheet range one row at a time using Google Apps Script


Tag : google-apps-script , By : ArdentRogue
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , Never 'push each row of the array, one at a time". This is clunky code and extremely slow to boot. Setting values it very similar to getting values. You declare a range variable with appropriates coordinates that fit the dimensions of your values[][] array, and pass the entire values array to 'setValues()' method of the Range object.
One of method signatures for getRange() is the following:
sheet.getRange(row, column, numberOfRows, numberOfColumns);
sheet.getRange(1, 1, sheetValues.length, sheetValues[0].length);
sheetValues = sheetValues.map(function(row){

if (row[9] == 'Target') { return row;  }

});
Related Posts Related QUESTIONS :
  • Looping in Creating PDF in Google Sheet
  • Is it possible to create a gmail label and choose its color using Google Apps Script?
  • Can I protect cells (user not allowed to edit) and run script that makes changes in these cells (e.g. hide row, unhides
  • Google Sheets custom sidebar: How to display a current cell value in sidebar without reload the sidebar?
  • oAuth disabled on google script
  • getOAuthToken returns null when called from Google Sheets
  • Use google-apps script url in project html
  • How to adjust as custom size margins and paper size in script to save google spreadsheet in PDF?
  • Google Apps Script HtmlServices template variables and global variables
  • What's the difference between a Page and a Slide in Google Slides?
  • How insert date from cell to google script?
  • Google Forms Add-On - menu item not appearing on initial load after installing plugin
  • How can I modify this function to use multiple tabs and columns for each tab?
  • Efficient way to check if an array of cells is not the desired value
  • Google sheet script starting from the 1000th line?
  • Google Script logger not showing what sheet edit was made on from event data
  • Is there a better way to make a copy of a spreadsheet with content only?
  • Increment column of cells by 1 if a condition is met
  • Need to delete an entire row based on one cell's criteria
  • Adding a few members to a Google Group in 1 URLFetchApp Call?
  • Referencing column heading text in form response spreadsheet in app script code
  • How can i set the correct answer to a question with AppScript?
  • Need help finding the maximum value in a column in Google Sheets using Google Apps Script
  • Creating array with change log data and sending it via email
  • Exit script if Google Sheet not opened or not modified today (within 24 hours)
  • Input name and check for existence of spreadsheet
  • Google sheets script for changing reference
  • Function onOpen not activating when i open my forms
  • Google Apps Script withSuccessHandler is running before function fully executes
  • Google App Script - Make numeric entries 0 if conditions in another column match
  • Get a Complete Score from Spreadsheet
  • setValue function executes, but values not updating
  • Google Forms response dashboard
  • How to allow all/ any extension files to upload in Google Drive
  • How to print a drive document with Google scripts
  • How to keep deleting cells based on their value
  • Send a mail with pdf attachment with help of Google script
  • Some questions about Google Data Studio and Google Apps Script
  • Google Apps Script - Sheets - IF statement with INCLUDES
  • google apps script - paste in next empty row
  • can i call time based trigger on gmail add-ons?
  • Return 2D array from Google Apps Script
  • Google Script to Clear multiple ranges.
  • Periodically refresh IMPORTXML() spreadsheet function
  • google-apps script user must give permission on OWN drive to open spreadsheet in app-server's drive?
  • Google Script pulling dynamic HTML iFrame from Google Sheet Cell
  • Approval button in mail from AppScript
  • Invalid BACKGROUND_COLOR?
  • Object logs null instead of calendar data
  • Splitting and Trimming Columns in Google Sheets Script?
  • Is it possible to create time driven triggers in Google Sheet Add On?
  • Trying to cross-check two ranges in Google Sheets with a script
  • Is it possible to "appendListItem" starting from a specific number?
  • How to check if a calendar event has been deleted when iterating through an array of id's in Google Sheets?
  • How to format the resource for Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId) to create a column group
  • how to let hangouts-chat bot talk to another user while greeted by first one?
  • Imported range time values dont match source range time values Google sheets script
  • Attaching alias signature with gmail api in google apps script
  • Comparison of individual Array Values
  • Servererror in Google App Script code
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com