CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-query-language
Abhik Paul
Abhik Paul

Reputation: 49

Preserve associated row data while using importRange in the destination sheet

For our construction company, we have a sheet that has all of the bills listed, along with relevant data that our accounting person would add to the master sheet.

I then have another sheet that pulls this data for the relevant people in the accounts for them to complete those steps. It filters to only the relevant columns (specifically, based on Column H - either "Yes" or "No") using query and importRange.

query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1pY53-XaGnUQ3BPmLh90mLSqIwSo7S2_QOPbD6JBQHOA/edit#gid=0","Master!A3:G"), "Select Col1, Col2, Col4, Col5, Col6, Col7 where Col6 is not null")

I want to include a few details in the destination sheet, which I have done.

The problem is typically associated with column H in the master sheet (Work Done required or not). For most cases, it is either a yes or no. However, in some cases, the accounting person doesn't know for sure whether it is a yes or no. But he wants to keep on adding other bill details.

When he fills the empty column later, the entered data on this second sheet doesn't dynamically shift with the imported data, thus causing the rows to misalign.

Unfortunately, as I mentioned, the rows don't stick together so as the dynamic order of the imported columns changes, the static order of the manual columns causes a mismatch.

Is there a way to make this work?

Upvotes: 0

Views: 1609

Answers (3)

Jonas Zeiger
Jonas Zeiger

Reputation: 178

Here is some Apps Script code (custom function + installable trigger) to solve this problem.

https://github.com/giantswarm/gapps-automation/tree/main/sticky-rows

It aligns two ranges on one sheet by performing something like an INNER JOIN using the first ranges ID colum (must be first column).

This way dynamic data (eg. from IMPORTRANGE() or QUERY()) can be used alongside user edited cells without falling apart, when row count or row order in the dynamic range change.

Upvotes: 0

ale13
ale13

Reputation: 6072

A solution would be to add an onEdit trigger in order to check if any changes have been made to the H column and later add the corresponding rows to the destination sheet, something similar to this:

function onEdit(e) {
  let destinationSheet = SpreadsheetApp.openById("SS_ID").getSheetByName('Input Sheet');
  let sourceSheet = SpreadsheetApp.getActiveSheet();
  let lastRow = destinationSheet.getLastRow();
  if (e.range.getColumn() == 8 && e.range.getValue() != null) {
    let billNo = sourceSheet.getRange(e.range.getRow(),1).getValue();
    // retrieve all the other values needed from the current row of the edit
    // current row of the edit > e.range.getRow()
    destinationSheet.getRange(lastRow+1,1).setValue(billNo);
    // copying the values to the destination sheet by using setValue()

  } 
}

The above code makes use of the e event object in order to check if the edit has been made in the H column and if the value is different from null. If this condition checks, the values needed to be copied are being retrieved (here I have illustrated how to retrieve the billNo) and later set them in the destination sheet by retrieving the last row of it.

Note

You can also add a for loop in order to manage the copying the data easier and also add more conditions such that it the new entry is being pasted after a certain Actual Date or simply sort the values after the entry is being pasted.

Reference

Upvotes: 1

Kin Siang
Kin Siang

Reputation: 2699

I have performed testing with the following formula, I found no issue even I remove certain value for certain cell in "Col H", and the result is still correct, am I miss out certain information? I just change the filter criteria based on Col H only

=query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1pY53-XaGnUQ3BPmLh90mLSqIwSo7S2_QOPbD6JBQHOA/edit#gid=0","Master!A3:H"),"Select Col1, Col2, Col4, Col5, Col6, Col7 where Col8 is not null")

Upvotes: 0

Related Questions