0

I want to move several rows from one sheet to another but all the scripts that I am looking at don't see to address this.

For example, I want to move Column A,B,C on Sheet1 to Column G,I,L on Sheet2.

Can this be done? I am very new to scripting and don't have an example of what I am doing.

Thanks.

baldalbino
  • 17
  • 1
  • 5
  • Sorry, move several columns not rows – baldalbino May 08 '15 at 20:33
  • Do you want to move as in, delete the data from the original location and paste it in the other sheet or just sort of copy the needed data and paste it to another location? – pointNclick May 08 '15 at 21:09
  • Ideally, I am looking to make and update on a different spreadsheet and move that data over to a second spreadsheet. The data would just be copied but I need to map to certain columns. – baldalbino May 11 '15 at 21:20

1 Answers1

5

This could be done with simple code:

function moveCols() {
var ss = SpreadsheetApp.getActive();
var sourceSheet = ss.getSheetByName('Sheet1');
var destSheet = ss.getSheetByName('Sheet2');
sourceSheet.getRange('A:A').moveTo(destSheet.getRange('G1'))
sourceSheet.getRange('B:B').moveTo(destSheet.getRange('I1'))
sourceSheet.getRange('C:C').moveTo(destSheet.getRange('L1'))
}

If you only want to copy the data (and not move it) change the .moveTo() method to copyTo().

EDIT: If you need to move the columns to another spreadsheet, try:

function moveCols() {
var sourceSheet = SpreadsheetApp.getActive()
    .getSheetByName('Sheet1'),
    destSheet = SpreadsheetApp.openById('ID_OF_DESTINATION_SHEET')
        .getSheetByName('Sheet2'),
    sourceRanges = ['A:A', 'B:B', 'C:C'],
    targetRanges = [7, 9, 12]
        .map(function (r, i) {
            var sr = sourceSheet.getRange(sourceRanges[i]),
                val = sr.getValues();
            destSheet.getRange(1, r, val.length, val[0].length)
                .setValues(val);
            sr.clear()
        });
}
JPV
  • 26,499
  • 4
  • 33
  • 48
  • Follow up question: Could I move from 2 different worksheets ? For example: move column A,B,C from Worksheet 1 Sheet 1 to G,H,I in Worksheet 2 Sheet 1? – baldalbino May 11 '15 at 17:29
  • When I attempt, it tells me that the Range must be in the same spreadsheet. – baldalbino May 11 '15 at 17:30
  • You want to move rows between two spreadsheets ? Yes, that is indeed possible. – JPV May 11 '15 at 18:23
  • Do I need to add the spreadsheet names like a formula? For example: "thisData.getRange('I2:I120').copyTo(toRange.getRange('A2'))" needs to be "thisData.getRange('Q2 2015 v2!I2:I120').copyTo(toRange.getRange('Raw Data!A2'))"? – baldalbino May 11 '15 at 20:36
  • No, that did not work. Answered my own question. But still working on the source and target range needing to be on the same spreadsheet. – baldalbino May 11 '15 at 20:56
  • Hello JPV - thanks for all your help. On line 9 - destSheet.getRange(r).setValues(sr.getValues()); - I am getting and error stating - Incorrect range height, was 182 but should be 1000 (line 9, file "Code"). Any ideas on that? I'll keep playing around with it and see if I can research what that means. – baldalbino May 12 '15 at 14:18
  • Maybe your second sheet has a different number of rows ? Anyway, I updated the second code with some changes. see if it works ? – JPV May 12 '15 at 17:52
  • That worked great. I see you changed the dest columns to numbers and that did the trick! – baldalbino May 12 '15 at 18:23
  • Ok, last question and I don't have enough rep to move to a chat. Sorry about that. If I wanted to copy the columns from Sheet 1 Tab 1 and then move via the script to Sheet 2 Tab 1, is there an updated way? When I update some things, it basically does not like the .map function (I believe). Is there a way that things will get copied over based off an update to Sheet 1 Tab 1? – baldalbino May 12 '15 at 19:04
  • I commented out the sr.clear() and it works without removing anything. – baldalbino May 12 '15 at 19:59
  • So did that answer your question ? – JPV May 12 '15 at 20:00
  • Yes it did. Thank you for helping me on that. I plan to learn more scripting but thank you for the big time assist. – baldalbino May 12 '15 at 21:13