Looking for a script to open a G sheet from a link and copy data inside it

[Loveday Lemon]

Hello everyone, maybe this is a big ask. In this sheet I have in column Z for each row, a link to an individual G sheet. 

For each line in this sheet, by hand, I'm opening the link in column Z, and then copying columns L to X to the first free row of the linked sheet, into columns A to N. 

I'm wondering if there is a script that could do that automatically. The difficulty is, I have empty lines sometimes above, so it would need in each sheet, to look for the first free row under the header row. For example, in this sheet the first free row is row 31, but it could be another row in another sheet. 

If it's not possible this way, I'd like to know if it becomes possible if I modify each linked sheet to that in Sheet1, there is not free row above the header row. 

I'd be grateful for any advice, thanks for reading!

[Paul Armstrong]

Hi

If the job is to get data from one spreadsheet into another and you have the URL of the source spreadsheet, then you can use the IMPORTRANGE function.

=IMPORTRANGE("<url of source spreadsheet>", "Sheet1!A1:C10")

[Brett Grear]

I added a sample script to your Test Spreadsheet.

It adds a functions menu in the top bar.

You can either select to copy just the selected Row or to Copy all

Is this what you were after?

[Ed Sambuco]

Actually, this is not a difficult script to write.  I outline it below as a standalone script.  I just desk checked this, and you will have to revise it in any case, but here goes:   B careful with the array vs cell indexing.  Kick indexes up by 1 if you have a header row.


function copySheetData() {
//  First, execute open of "top" spreadsheet
          var ssTop = SpreadsheetApp.openByUrl(URL of the sheet with links in the Z column)'
//  Get the data from the spreadsheet into an array:
          var topArray = ss.getRange(1,1, ssTop.getLastRow(),ssTop.getLastColumn()).getValues();
//  Set up a loop through topArray
          for(i = 0; i < topArray.length; i++) {

-         //Inside the loop, open the spreadsheet indicated in column Z
                 var ssFromZ = SpreadsheetApp.openByUrl(topArray(i,25));  // column Z is at index position 25.
           
          //  Get values from top spreadsheet, data from row i + 1, columns L to X.
          //  For arrays, index starts at 0; for cell reference, index starts at 1
              var dataFromTop = ssTop.getRange(i + 1,11,1,12).getValues(); 
              // Skip empty row
                  if dataFromTop(i,0) ==  ''  {
                     continue;
                  }
              // Then load to subordinate spreadsheet. data goes to row 1 of subordinate sheet, columns A to N 
         
                  ssFromZ.getRange(1,1,1,12).setValues(dataFromTop); 
          }

}                        

             

--
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/840ac939-17f6-46a0-97bf-72debd80dd22n%40googlegroups.com.

[Loveday Lemon]

Thanks all, I'm very grateful for your replies, I will look at your suggestions and report back!

[Loveday Lemon]

Hi all, thank you so much, this works perfectly! I had actually made a mistake in my request, I needed to copy rows L to Y and not L to X, but with your explanations I was able to change the script and include the missing column. Thanks again I'm very grateful! 

[Poonam Shrivastava]

Hi, Is it possible to share the working script with me. I do have the similar data copy job to be done.

[Loveday Lemon]

Sure, here is the link :

https://docs.google.com/spreadsheets/d/1XaDR37c-KzFkGuzPLuzgbjOAbI6_TGSvJs-qQTHsKbo/edit#gid=1686302869

[Shrivastava, Poonam]

Hi,

Please provide the access to the sheet.

Thank you

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0bb33d96-a507-456e-a922-b736f1ac84f6n%40googlegroups.com.

[Loveday Lemon]

Hello, I made it editable by anyone with the link.