Ggtcf

Is there a minimum number of transactions in a block?

Are white and non-white police officers equally likely to kill black suspects?

My colleague's body is amazing

Accidentally leaked the solution to an assignment, what to do now? (I'm the prof)

Showing the closure of a compact subset need not be compact

How to use Pandas to get the count of every combination inclusive

Can an x86 CPU running in real mode be considered to be basically an 8086 CPU?

What Brexit solution does the DUP want?

Factorio analysis: data munging

DOS, create pipe for stdin/stdout of command.com(or 4dos.com) in C or Batch?

Set-theoretical foundations of Mathematics with only bounded quantifiers

A newer friend of my brother's gave him a load of baseball cards that are supposedly extremely valuable. Is this a scam?

How do I create uniquely male characters?

Where to refill my bottle in India?

Why do UK politicians seemingly ignore opinion polls on Brexit?

Pristine Bit Checking

Why doesn't Newton's third law mean a person bounces back to where they started when they hit the ground?

"listening to me about as much as you're listening to this pole here"

Why is "Reports" in the sentence below without the article "The"?

Is it legal to have the "// (c) 2019 John Smith" header in all files when there are hundreds of contributors?

"The augmented fourth (A4) and the diminished fifth (d5) are the only augmented and diminished intervals that appear in diatonic scales"

LM317 - Calculate dissipation due voltage drop

Why has Russell's definition of numbers using equivalence classes been finally abandoned? ( If it has actually been abandoned).

How to create a consistant feel for character names in a fantasy setting?

copy dynamic range from one spreadsheet to another spreadsheet called with getfileid

App script to loop though Google Sheets and cel range then gets a matching sheet to file id then copy data to id fileGoogleScript Spreadsheet Custom Function Handling a range of cells and getting their valuesCopy a google spreadsheet to another in a reverse orderIssue with Spreadsheet Script using openByIdDrag and copy range vertically from spreadsheet googleGoogle spreadsheet SUMIF() dynamic range functionGoogle sheets splitting cells from multiple columns into multiple rows — formulas not workingSpreadsheet with relationshipsCopying styles from a range to another range?Errors with using && in a WHILE LOOP for Google AppscriptApp script to loop though Google Sheets and cel range then gets a matching sheet to file id then copy data to id file

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

my sample spreadsheet I need a hand with my codes.

Task: Loop through column "A" in "Customer" and get corresponding sheet name. Get the sheet and copy all used range in sheet, e.g (A;B;C;etc) to the specific files referenced in column B. That's it - simple right. Well I am as green as a sapling when it comes to app script. i spent all weekend and yesterday on this (very interesting).

My effort so far

function updatefile() 
 var ss = SpreadsheetApp.getActive();
 var sh = ss.getSheetByName('CUSTOMER')
 var data = sh.getRange("A2:B12").getValues(); // get the range of non emmpty cells
 for (var i = 1; i < data.length; i++) 
 var source = ss.getSheetByName(data[i][0]);
 var range = source.getRange('C3:M1003'); //assign the range you want to copy
 var data = range.getValues();

 var destination = SpreadsheetApp.openById(data[i][1]); // open the 
 coresponding file by using the file id in the secound column 
 var destbodya = destination.getSheetByName('UPDATE ON ORDERS');
 destbodya.getRange(ts.getLastRow()+1, 1,49,7).setValues(data); //you will need to define the size of the copied data see getRange()

spreadsheet appscript

share|improve this question

edited Feb 26 at 20:02

Vinyking

asked Feb 26 at 17:26

VinykingVinyking

13

add a comment | 

0

my sample spreadsheet I need a hand with my codes.

Task: Loop through column "A" in "Customer" and get corresponding sheet name. Get the sheet and copy all used range in sheet, e.g (A;B;C;etc) to the specific files referenced in column B. That's it - simple right. Well I am as green as a sapling when it comes to app script. i spent all weekend and yesterday on this (very interesting).

My effort so far

function updatefile() 
 var ss = SpreadsheetApp.getActive();
 var sh = ss.getSheetByName('CUSTOMER')
 var data = sh.getRange("A2:B12").getValues(); // get the range of non emmpty cells
 for (var i = 1; i < data.length; i++) 
 var source = ss.getSheetByName(data[i][0]);
 var range = source.getRange('C3:M1003'); //assign the range you want to copy
 var data = range.getValues();

 var destination = SpreadsheetApp.openById(data[i][1]); // open the 
 coresponding file by using the file id in the secound column 
 var destbodya = destination.getSheetByName('UPDATE ON ORDERS');
 destbodya.getRange(ts.getLastRow()+1, 1,49,7).setValues(data); //you will need to define the size of the copied data see getRange()

spreadsheet appscript

share|improve this question

edited Feb 26 at 20:02

Vinyking

asked Feb 26 at 17:26

VinykingVinyking

13

add a comment | 

my sample spreadsheet I need a hand with my codes.

Task: Loop through column "A" in "Customer" and get corresponding sheet name. Get the sheet and copy all used range in sheet, e.g (A;B;C;etc) to the specific files referenced in column B. That's it - simple right. Well I am as green as a sapling when it comes to app script. i spent all weekend and yesterday on this (very interesting).

My effort so far

function updatefile() 
 var ss = SpreadsheetApp.getActive();
 var sh = ss.getSheetByName('CUSTOMER')
 var data = sh.getRange("A2:B12").getValues(); // get the range of non emmpty cells
 for (var i = 1; i < data.length; i++) 
 var source = ss.getSheetByName(data[i][0]);
 var range = source.getRange('C3:M1003'); //assign the range you want to copy
 var data = range.getValues();

 var destination = SpreadsheetApp.openById(data[i][1]); // open the 
 coresponding file by using the file id in the secound column 
 var destbodya = destination.getSheetByName('UPDATE ON ORDERS');
 destbodya.getRange(ts.getLastRow()+1, 1,49,7).setValues(data); //you will need to define the size of the copied data see getRange()

spreadsheet appscript

share|improve this question

edited Feb 26 at 20:02

Vinyking

asked Feb 26 at 17:26

VinykingVinyking

13

function updatefile() 
 var ss = SpreadsheetApp.getActive();
 var sh = ss.getSheetByName('CUSTOMER')
 var data = sh.getRange("A2:B12").getValues(); // get the range of non emmpty cells
 for (var i = 1; i < data.length; i++) 
 var source = ss.getSheetByName(data[i][0]);
 var range = source.getRange('C3:M1003'); //assign the range you want to copy
 var data = range.getValues();

 var destination = SpreadsheetApp.openById(data[i][1]); // open the 
 coresponding file by using the file id in the secound column 
 var destbodya = destination.getSheetByName('UPDATE ON ORDERS');
 destbodya.getRange(ts.getLastRow()+1, 1,49,7).setValues(data); //you will need to define the size of the copied data see getRange()

share|improve this question

edited Feb 26 at 20:02

Vinyking

asked Feb 26 at 17:26

VinykingVinyking

13

share|improve this question

edited Feb 26 at 20:02

Vinyking

asked Feb 26 at 17:26

VinykingVinyking

13

asked Feb 26 at 17:26

VinykingVinyking

13

asked Feb 26 at 17:26

VinykingVinyking

13

1 Answer
1

active

oldest

votes

0

I finally got this to work after a lot of searching and trials. for those who comes by such challenges. Cheers

function updatefile() 
 var ss = SpreadsheetApp.getActive();
 var sh = ss.getSheetByName('CUSTOMER')
 var data = sh.getRange("A2:B73").getValues(); // get the range of non emmpty cells
 for (var i = 1; i < data.length; i++) 
 if (data[i][0] != '') 
 var source = ss.getSheetByName(data[i][0]);
 var range = source.getRange('C3:M1003'); //assign the range you want to copy
 var valuedata = range.getValues();

 //var destination = SpreadsheetApp.openById("xxxxxxxxxxxxrrrrrsomeID"); // enter file ID directly or the nest line
 var destination = SpreadsheetApp.openById(data[i][1]); // Iterate through the range and open the corresponding file by using the file id in the secound column 
 var dest_1body = destination.getSheetByName('blah blah sheetname'); // first sheet destination to copy data to
 dest_1body.getRange("A2:K1002").setValues(valuedata); //you will need to define the size of the copied data see getRange()

 var destination = SpreadsheetApp.openById(data[i][1]); // open the coresponding file by using the file id in the secound column
 var dest_2body = destination.getSheetByName('blah blah sheetname'); // second sheet to copy same data to

 dest_2body.getRange("A28:K1028").setValues(valuedata); //you will need to define the size of the copied data see getRange()
 
 

share|improve this answer

edited Mar 9 at 4:02

Tedinoz

1,34721419

answered Feb 27 at 4:02

VinykingVinyking

13

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Line4: the range A2:B73 creates a data.length of 72 but there are only 11 values in the range. You might be better off getting the last row ` var lastRow = sh.getLastRow();` and use that to get the range Line5: when you got the range in line 4, you already took into account the header in row 1. So var i = 1 skips Customer A completely. It should be: var i = 0 Line8: is this an actual range or a guesstimate? Line12: var destination = SpreadsheetApp.openById(data[i][1]); fails should be data[1][i]. Line16/17: Isn't this a duplication of Line12/13?
  
  – Tedinoz
  Mar 9 at 3:06
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54891012%2fcopy-dynamic-range-from-one-spreadsheet-to-another-spreadsheet-called-with-getfi%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

I finally got this to work after a lot of searching and trials. for those who comes by such challenges. Cheers

function updatefile() 
 var ss = SpreadsheetApp.getActive();
 var sh = ss.getSheetByName('CUSTOMER')
 var data = sh.getRange("A2:B73").getValues(); // get the range of non emmpty cells
 for (var i = 1; i < data.length; i++) 
 if (data[i][0] != '') 
 var source = ss.getSheetByName(data[i][0]);
 var range = source.getRange('C3:M1003'); //assign the range you want to copy
 var valuedata = range.getValues();

 //var destination = SpreadsheetApp.openById("xxxxxxxxxxxxrrrrrsomeID"); // enter file ID directly or the nest line
 var destination = SpreadsheetApp.openById(data[i][1]); // Iterate through the range and open the corresponding file by using the file id in the secound column 
 var dest_1body = destination.getSheetByName('blah blah sheetname'); // first sheet destination to copy data to
 dest_1body.getRange("A2:K1002").setValues(valuedata); //you will need to define the size of the copied data see getRange()

 var destination = SpreadsheetApp.openById(data[i][1]); // open the coresponding file by using the file id in the secound column
 var dest_2body = destination.getSheetByName('blah blah sheetname'); // second sheet to copy same data to

 dest_2body.getRange("A28:K1028").setValues(valuedata); //you will need to define the size of the copied data see getRange()
 
 

share|improve this answer

edited Mar 9 at 4:02

Tedinoz

1,34721419

answered Feb 27 at 4:02

VinykingVinyking

13

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Line4: the range A2:B73 creates a data.length of 72 but there are only 11 values in the range. You might be better off getting the last row ` var lastRow = sh.getLastRow();` and use that to get the range Line5: when you got the range in line 4, you already took into account the header in row 1. So var i = 1 skips Customer A completely. It should be: var i = 0 Line8: is this an actual range or a guesstimate? Line12: var destination = SpreadsheetApp.openById(data[i][1]); fails should be data[1][i]. Line16/17: Isn't this a duplication of Line12/13?
  
  – Tedinoz
  Mar 9 at 3:06
  
  

  
  
  
  

add a comment | 

0

I finally got this to work after a lot of searching and trials. for those who comes by such challenges. Cheers

function updatefile() 
 var ss = SpreadsheetApp.getActive();
 var sh = ss.getSheetByName('CUSTOMER')
 var data = sh.getRange("A2:B73").getValues(); // get the range of non emmpty cells
 for (var i = 1; i < data.length; i++) 
 if (data[i][0] != '') 
 var source = ss.getSheetByName(data[i][0]);
 var range = source.getRange('C3:M1003'); //assign the range you want to copy
 var valuedata = range.getValues();

 //var destination = SpreadsheetApp.openById("xxxxxxxxxxxxrrrrrsomeID"); // enter file ID directly or the nest line
 var destination = SpreadsheetApp.openById(data[i][1]); // Iterate through the range and open the corresponding file by using the file id in the secound column 
 var dest_1body = destination.getSheetByName('blah blah sheetname'); // first sheet destination to copy data to
 dest_1body.getRange("A2:K1002").setValues(valuedata); //you will need to define the size of the copied data see getRange()

 var destination = SpreadsheetApp.openById(data[i][1]); // open the coresponding file by using the file id in the secound column
 var dest_2body = destination.getSheetByName('blah blah sheetname'); // second sheet to copy same data to

 dest_2body.getRange("A28:K1028").setValues(valuedata); //you will need to define the size of the copied data see getRange()
 
 

share|improve this answer

edited Mar 9 at 4:02

Tedinoz

1,34721419

answered Feb 27 at 4:02

VinykingVinyking

13

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Line4: the range A2:B73 creates a data.length of 72 but there are only 11 values in the range. You might be better off getting the last row ` var lastRow = sh.getLastRow();` and use that to get the range Line5: when you got the range in line 4, you already took into account the header in row 1. So var i = 1 skips Customer A completely. It should be: var i = 0 Line8: is this an actual range or a guesstimate? Line12: var destination = SpreadsheetApp.openById(data[i][1]); fails should be data[1][i]. Line16/17: Isn't this a duplication of Line12/13?
  
  – Tedinoz
  Mar 9 at 3:06
  
  

  
  
  
  

add a comment | 

I finally got this to work after a lot of searching and trials. for those who comes by such challenges. Cheers

function updatefile() 
 var ss = SpreadsheetApp.getActive();
 var sh = ss.getSheetByName('CUSTOMER')
 var data = sh.getRange("A2:B73").getValues(); // get the range of non emmpty cells
 for (var i = 1; i < data.length; i++) 
 if (data[i][0] != '') 
 var source = ss.getSheetByName(data[i][0]);
 var range = source.getRange('C3:M1003'); //assign the range you want to copy
 var valuedata = range.getValues();

 //var destination = SpreadsheetApp.openById("xxxxxxxxxxxxrrrrrsomeID"); // enter file ID directly or the nest line
 var destination = SpreadsheetApp.openById(data[i][1]); // Iterate through the range and open the corresponding file by using the file id in the secound column 
 var dest_1body = destination.getSheetByName('blah blah sheetname'); // first sheet destination to copy data to
 dest_1body.getRange("A2:K1002").setValues(valuedata); //you will need to define the size of the copied data see getRange()

 var destination = SpreadsheetApp.openById(data[i][1]); // open the coresponding file by using the file id in the secound column
 var dest_2body = destination.getSheetByName('blah blah sheetname'); // second sheet to copy same data to

 dest_2body.getRange("A28:K1028").setValues(valuedata); //you will need to define the size of the copied data see getRange()
 
 

share|improve this answer

edited Mar 9 at 4:02

Tedinoz

1,34721419

answered Feb 27 at 4:02

VinykingVinyking

13

function updatefile() 
 var ss = SpreadsheetApp.getActive();
 var sh = ss.getSheetByName('CUSTOMER')
 var data = sh.getRange("A2:B73").getValues(); // get the range of non emmpty cells
 for (var i = 1; i < data.length; i++) 
 if (data[i][0] != '') 
 var source = ss.getSheetByName(data[i][0]);
 var range = source.getRange('C3:M1003'); //assign the range you want to copy
 var valuedata = range.getValues();

 //var destination = SpreadsheetApp.openById("xxxxxxxxxxxxrrrrrsomeID"); // enter file ID directly or the nest line
 var destination = SpreadsheetApp.openById(data[i][1]); // Iterate through the range and open the corresponding file by using the file id in the secound column 
 var dest_1body = destination.getSheetByName('blah blah sheetname'); // first sheet destination to copy data to
 dest_1body.getRange("A2:K1002").setValues(valuedata); //you will need to define the size of the copied data see getRange()

 var destination = SpreadsheetApp.openById(data[i][1]); // open the coresponding file by using the file id in the secound column
 var dest_2body = destination.getSheetByName('blah blah sheetname'); // second sheet to copy same data to

 dest_2body.getRange("A28:K1028").setValues(valuedata); //you will need to define the size of the copied data see getRange()
 
 

share|improve this answer

edited Mar 9 at 4:02

Tedinoz

1,34721419

answered Feb 27 at 4:02

VinykingVinyking

13

edited Mar 9 at 4:02

Tedinoz

1,34721419

edited Mar 9 at 4:02

Tedinoz

1,34721419

answered Feb 27 at 4:02

VinykingVinyking

13

answered Feb 27 at 4:02

VinykingVinyking

13