0

So I have a google spreadsheet. It has about 15 sheets in it. I want to copy the cell value from all of these 15 sheets to another workbook.

I tried using the importrange function. The values I am trying to copy are dynamic that is based on formula which calculates it.

so I tried using the function export 

function copy(){

 var sss = SpreadsheetApp.openById('14wsBaW2HWqtdPZBDycsRVQCgOJuaNIYDKDJOjTDwWO8'); //replace with source ID
 var ss = sss.getSheetByName('Round 1'); //replace with source Sheet tab name

 var range = ss.getRange('G14'); //assign the range you want to copy
 var data = range.getValues();

 var tss = SpreadsheetApp.openById('1SWOZ_Xq-1E3LHfE1FwTt4O9bs-FIAk3I5Wjz9VKFh6A'); //replace with destination ID
 var ts = tss.getSheetByName('Net Worth'); //replace with destination Sheet tab name
 ts.getRange(2, 2, data.length, data[0].length).setValues(data);


 var sss = SpreadsheetApp.openById('14wsBaW2HWqtdPZBDycsRVQCgOJuaNIYDKDJOjTDwWO8'); //replace with source ID
 var ss = sss.getSheetByName('Round 2'); //replace with source Sheet tab name

 var range = ss.getRange('G14'); //assign the range you want to copy
 var data = range.getValues();

 var tss = SpreadsheetApp.openById('1SWOZ_Xq-1E3LHfE1FwTt4O9bs-FIAk3I5Wjz9VKFh6A'); //replace with destination ID
 var ts = tss.getSheetByName('Net Worth'); //replace with destination Sheet tab name
 ts.getRange(3, 2, data.length, data[0].length).setValues(data);

}

NOW as you can see since I have to copy the values from about 15 sheets of the workbook I have to write multiple lines of the copy paste function by changing the Round i( where i=1-15) and the row number where it is posted.

What I want to achieve is to reduce the size of code and base it on time trigger or on open or on edit trigger. However, I am stuck and can't find much

Jack Brown
  • 5,802
  • 2
  • 12
  • 27
hashim diwan
  • 11
  • 2

1 Answers1

1

You can get all the sheets on the book and loop through each of them to copy the value in 'G14' cell. Save them in a 2D array. Refer the code below.

 ////copying values from each sheet
 var copyBook = SpreadsheetApp.getActiveSpreadsheet();
 var copySheets = copyBook.getSheets();
 var selectedValues = []; 
 for(var sheet in copySheets)
 {
   //Logger.log(copySheets[sheet].getName());
   var value = copySheets[sheet].getRange('G14').getValue();
   selectedValues.push([value]);
 }
 //Logger.log(selectedValues);

Now your values are copied in the array and now you want to paste it your destination sheet.

////pasting copied values
 var pasteBook = SpreadsheetApp.openById('destination book id'); 
 var pasteSheet = pasteBook.getSheetByName('destination sheet name');
 pasteSheet.getRange(2, 2, selectedValues.length, 1).setValues(selectedValues);

If you want to set a trigger, refer this answer: https://stackoverflow.com/a/34684370/1134291

replace above code with // your work to be done section

iJay
  • 4,205
  • 5
  • 35
  • 63