Copy specific columns form several sheet to master

0
votes

i have a Master and several tabs am using a Query to copy ( Mirror ) specific columns as below :

select Col1,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13 where Col1 is not null

and the sheets were 12 sheet populating the data to the Master

while at the same time i have a script which i used to remove any work done in the master to another sheet, the issue is anytime i remove the entire row it returns back to populate in the master due to the query

i was willing to use a script to copy the same work as the query but one time only, as if i want to delete any specific row from the master will not be copy again :

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var source = ss.getRange ("Sheet1!A4:M","Sheet2!A4:M","Sheet3!A4:M");
  var destSheet = ss.getSheetByName("Master");
  var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);
  source.copyTo (destRange, {contentsOnly: true})}

which is not working correctly, your help and advice will highly appriciated.

1
javascriptgoogle-apps-scriptgoogle-sheetscopy
It's unclear to me which Spreadsheet app you are using, since this is not pure javascript or jquery as marked in the question. I think I got your question you just want a one time action to copy the data from 12 sheets into 1, but not synced? But without knowing the Spreadsheet application and in which context this is running, I think nobody can answer this question. – overflowed
@overflowed, appriciated your kind response actually am adding this script in google sheet while i was using originally query to pull the data : QUERY({JAN!A4:M;FEB!A4:M;MAR!A4:M;APR!A4:M;MAY!A4:M;JUN!A4:M;JUL!A4:M;AUG!A4:M;SEP!A4:M;OCT!A4:M;NOV!A4:M;DEC!A4:M},"select Col1,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13 where Col1 is not null",0) what am looking for as you figure it out, i want one time copy and if i delete or move the data from the master will not be populated again, anticipate your kind help with this matter, Thanks – Adabsiz

1 Answers

1
votes

Explanation:

The following script will copy the data in A4:M from every sheet specified in the sheetNames array to the Master sheet.

  • forEach sheet specified in sheetNames get the values in A4:M and add concatenate them iteratively to a current array data. In this way, data will contain the values of all selected sheets in the range A4:M.

  • Then set the values to the Master sheet, starting from cell A1. Feel free to change that part:

    destSheet.getRange(1,1,data.length,data[0].length).setValues(data); // cell A1 (1,1)

Solution:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const destSheet = ss.getSheetByName("Master");
  const sheetNames = ['Sheet1','Sheet3']; //add sheets here
  let data = [];
  sheetNames.forEach(shn=>{
     let sh = ss.getSheetByName(shn);
     let mR = sh.getRange('A4:A').getValues().filter(String).length+3;
     let temp = sh.getRange('A4:M'+mR).getValues().
                map(arr =>  arr.filter((_,c) => ![1,2,3].includes(c)));
     data=data.concat(temp);
  });
  destSheet.getRange(4,1,data.length,data[0].length).setValues(data);
};