QUERY IMPORTRANGE while keeping row position-CodePudding

I have two spreadsheets, one client facing and one internal facing, that use IMPORTRANGE and QUERY IMPORTRANGE to display data between one another.

The internal sheet is used to write copies for social media posts, quality check them and give approval. Once approval is given the client sheet needs to update to show the copies on the identically named pages AND in the correct rows.

Approval is given by ticking a checkbox in the internal sheet and the QUERY IMPORTRANGE formula then pulls that into the client sheet. However, my abilities stop where I have to display this in the correct row position no matter which of the tickboxes are checked.

=iferror(QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1CIXhcuTigYbggmPjBKpvnH7P-cjBDQfSIcmKN6_0T5M/edit#gid=2018991957","Cloud in FS Survey!C2:N"),"SELECT Col1, Col2, Col3, Col4, Col5, Col6 WHERE Col11 = TRUE"),"No copies approved")

I've seen some discussions of having a second sheet for of the categories (there are 8 in the non-redacted document) and using VLOOKUP to match an ID to the imported row of data, hence I've created 'Row' columns in all tabs on both sheets with the unique row number. However, I want to avoid having hidden tabs in the client facing sheet where they can be revealed by the client.

Any assistance would be appreciated.

Many thanks,

Mark

CodePudding user response：

There are 2 ways to deal with it. If you a add serial number in sheet 1 where approvals are given, you may add another element to your query by adding order by serial no. column ASC

Else you could do the same with order by date column ASC

This way new additions will add below the imported data because they will always be in chronology and not mess up the order of older data.

Having direct importranges where static data exists is always risky.

Alternately, you could also use ID thing without creating an additional hidden sheet, direct vlookups with importrange nested inside the vlookup.

For example, =arrayformula(vlookup(search key, importrangexxx, index, false)

The best solution would be to have a hidden sheet. But all of the above can be decent alternate if you're not dealing with thousands of rows.

CodePudding user response：

If you don't want to use helper sheet or column, you can use Apps Script and onEdit Trigger.

Using onEdit Trigger, you can run a function automatically when a user click the checkbox. Inside that function is a list of commands that will write data to the client side.

Example:

To start with Apps Script:

Go to Tools -> Script Editor.
In your script editor, delete the code in Code.gs
Paste the code provided below.
Click Save.

Code:

function showToClientSide(e) {
  var val = e.value;
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();
  var sheet = range.getSheet();
  var clientSS = SpreadsheetApp.openById("Insert Client Sheet ID here")
  if(val == "TRUE" && row > 1 && col == 13){ // check if checkbox is checked
    var data = sheet.getRange(row, 3, 1, 9).getValues(); //get data from internal
    var sh = clientSS.getSheetByName("Cloud in FS Survey"); //client sheet
    sh.getRange(row, 3, 1, 9).setValues(data); //write internal data to client
  }else if(val == "FALSE" && row > 1 && col == 13){
    var sh = clientSS.getSheetByName("Cloud in FS Survey"); //client 
    sh.getRange(row, 3, 1, 9).clearContent(); //delete data when unchecked
  }
}

To setup your Installable Trigger (onEdit):