Is it possible to Insert a row and paste to a "non-Active" sheet

[Jim U]

I am very new to this and  trying to create and auction file where I auction something on one sheet and copy that info to another, then reset the original sheet to be ready for the next auction item. I always want the latest sold item in row 2 of the 'Sold' sheet, so the latest sold item will be easily found. 

I recorded a macro and create a script that works for the most part, but it's a little clunky because the script starts in my auction sheet opens the 'Sold' sheet and then back to the Auction sheet.

Is there any way to not 'Activate' the Sold sheet and just do the functions I am trying to do there 'behind the scene'. 

Here is the script I have. The highlighted section is what I want to happen without actually setting the 'Sold' spreadsheet to Active (I'm assuming this is what part of the script opens that sheet):

function Sold() {

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getRange('A2:G2').activate();

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sold'), true);

  spreadsheet.getRange('2:2').activate();

  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);

  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();

  spreadsheet.getRange('A2').activate();

  spreadsheet.getRange('Auction!A2:G2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getRange('H1').activate();

  spreadsheet.getCurrentCell().setFormula('=G2+1');

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Auction'), true);

  spreadsheet.getRange('G2').activate();

  spreadsheet.getRange('Sold!H1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getRange('B2:F2').activate();

  spreadsheet.setCurrentCell(spreadsheet.getRange('F2'));

  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});

  spreadsheet.getRange('B2').activate();

};

Any help would be appreciated.  I've looked around and tried a few things, but being new to coding I'm probably missing some very basic steps to achieve this. 

Thanks in advance.

Jim

[Laurie J. Nason]

Welcome Jim to the dim and dark underworld of Appsscripting!!!!

Absolutely yes…… (disclaimer….. untested code)

    var auctionSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Auction');

    var soldSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(’Sold');

Then you need to work out how to get the range e.g. ‘A2:G2' you want to copy and just replace my_range

    var copyRange= auctionSheet.getRange(‘A2:G2’).getValues();

Next work out how you get the range you want to copy into which you said was row 2 of the sold sheet

So you will insert a row before row 2 on that sheet

    soldSheet.insertRowBefore(2);

Then it looks like you are inserting the values starting at column H (col 8) in that row

soldSheet.getRange(2,8,1,copyRange[0].length).setValues(copyRange);

Finally - clear the range you copied in the auction sheet

    auctionSheet.getRange(‘A2:G2’).clear();

I think that should do it!

Laurie  

------ Original Message ------

From "Jim U" <julbri...@gmail.com>

To "Google Apps Script Community" <google-apps-sc...@googlegroups.com>

Date 11/21/2022 5:54:06 AM

Subject [Apps-Script] Is it possible to Insert a row and paste to a "non-Active" sheet

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8519b1d8-d223-4c05-910d-f4eab3b6e5ean%40googlegroups.com.

[Jim U]

Hi Laurie,

Thanks for the help.  This did not seem to help.  When  paste in your first two lines, I get errors "Syntax error: Invalid or unexpected token Line 4..." Line 4 is the first line you sent over:

 var auctionSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Auction');

 var soldSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(’Sold');

Thanks again.

Jim

[Jim U]

OK so I'm not sure what was going on this afternoon, but I tried this again tonight and using the first two rows above did help.  

I also found a quick way to copy/paste in the data by using:

var range = auction.getRange("A2:G2");

range.copyValuesToRange(soldSheet, 1, 7, 2, 2);

Thanks again for the help and for pointing me in the right direction.

Jim

[Laurie J. Nason]

No worries!

------ Original Message ------

From "Jim U" <julbri...@gmail.com>

To "Google Apps Script Community" <google-apps-sc...@googlegroups.com>

Date 11/22/2022 7:32:05 AM

Subject Re: [Apps-Script] Is it possible to Insert a row and paste to a "non-Active" sheet

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bd17a633-294d-4786-bfc3-3f134e887f4dn%40googlegroups.com.