CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
LTi-GT
LTi-GT

Reputation: 62

Conditionally copy data from one sheet to another with variable condition

I am trying to figure out how I can write rows from one sheet to another if the value of, for example A4 on the destination sheet corresponds with a value in column A in the Source sheet. However, I only need to copy the last 19 columns and the columns in front of that are not needed. I do know how I can copy the wanted rows but the condition seems to be a constant value instead of a value that changes for every row in the destination sheet (so A4 value is different than A5, A6, etc.) I took a bit of the function from my sheet and copied it in here. 

function getPDdataupper() {
 var sheet = SpreadsheetApp.getActive().getSheetByName('SPR');

 var folder= DriveApp.getFolderById('Folder-id');
 var files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
 while (files.hasNext()) {
   var ssraw = SpreadsheetApp.open(files.next()); 
 }
    sheet.getRange(11,1,(sheet.getLastRow())-10,53).clearContent();
var sheetraw = ssraw.getSheetByName("SPR");
  var sheetl=SpreadsheetApp.getActive().getSheetByName('single line data1')    

var data = sheetl.getDataRange().getValues();{
            var val = new Array();
           for(n=0;n<data.length;++n){
            if (data[n][1]=="'SPR'!A4");{ 
              val.push([[data[n][24]], [data[n][25]], [data[n][26]], [data[n][27]], [data[n][28]], [data[n][29]], [data[n][30]], [data[n][31]],
                [data[n][32]], [data[n][33]], [data[n][34]], [data[n][35]], [data[n][36]], [data[n][37]], [data[n][38]], [data[n][39]], [data[n][40]], [data[n][41]], [data[n][42]], [data[n][43]]]);
              }
          }


            sheet.getRange(7,35,1,1).setValues(val);

    }

}

If someone could help me into the right direction to get the condition changed by every row it checks that would be great. Also, i'm not sure if I can use an Array now or if I have to use Appendrow.

Thanks, Tim

Upvotes: 0

Views: 1459

Answers (1)

nabnub
nabnub

Reputation: 41

Have a look at this:

PULLING SPREADSHEET DATA: NO SCRIPTS REQUIRED

Once you've worked out what to your function needs to do, if you try it in a cell you'll get an error, so follow the steps below:

Imagine your full function is something like that:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1E5-hvA-Lw-BpYWhYf8Ib7u78oevW3789Z8m1vQ8HU/edit#gid=537281810","Sheet1!A3:R800"), "Select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17, Col18 where Col3 ='Late'")

1st step: write in the first cell where you want your data to be imported: 

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1E5-hvA-Lw-BpYWhYf8Ib7u78oevW3789Z8m1vQ8HU/edit#gid=537281810","Sheet1!A3:R800")

You'll see an error within the cell and if you pass your mouse over it you get 'You need to connect these sheets.' So you press 'Allow access'

2nd step: you add/complete what was missing from the function (like in the example above (=QUERY( etc.. Select Col1, Col2, Col3 etc...)

Hope this helps

Upvotes: 1

Related Questions