Google-sheets – Query, Import Range and Concat

concatenategoogle sheetsimportrange

I'm wanting to import whole rows of data from 'SPREADSHEET A' to 'SPREADSHEET B' but only if Column 11 in 'SPREADSHEET A' contains the text 'EXPIRED'.

The data in 'SPREADSHEET A' contains about 12 columns and numerous rows, with each cell containing either text, a number, or are blank.

When each row of data from 'SPREADSHEET A' is transferred, I want the imported information concated across each column for each row, so that it looks like an uninterrupted sentence when it's transferred (Meaning that blank cells are skipped).

Is this possible? Or should I cut my loses?

Best Answer

Try this...

=filter(arrayformula(trim(Sheet1!A1:A&" "&Sheet1!B1:B&" "&Sheet1!C1:C&" "&Sheet1!D1:D&" "&Sheet1!E1:E&" "&Sheet1!F1:F&" "&Sheet1!G1:G&" "&Sheet1!H1:H&" "&Sheet1!I1:I&" "&Sheet1!J1:J&" "&Sheet1!K1:K&" "&Sheet1!L1:L)),Sheet1!K1:K="Expired")

Using the filter function in SpreadsheetB by referencing SpreadsheetA is what can bring the data between the two sheets only when Col11 = "Expired"

The concatenate part is a bit manual because you can't use concatenate() or join() with the filter (or arrayformula) function to output the concatenate per row as you requested. So you basically need to manually concatenate each column with the & to allow the arrayformula() to read it correctly.

You mentioned you wanted it to read like a sentence, so I included a space between each cell (this is the &" "&). However, you also said you want to ignore blank cells. This formula will include a blank cell and will look like an extra space in your "sentence" Therefore, I also included the trim() function which will reconcile any leading, trailing, or duplicate spaces between.

Here is a sheet with a sample for you to copy and use. https://docs.google.com/spreadsheets/d/1c7N4bhY2qVetvdvEdbH86hSaT7T0TMUafV8yyuYMmh4/edit?usp=sharing

EDIT: For clarification you may have meant spreadsheet like two different unique google sheets which is why you included importrange() in your question. While you can inject importrange() into each range that you need to concatenate in this formula. That might be a bit much. So my suggestion would be to simply importrange() the whole contents of SpreadsheetA into a sheet in B, and use the above to cut it down to what you need. But I'm not clear if that is what you meant or not.

Related Solutions

Google-sheets – How to add blank rows after each item from a FILTER() or QUERY() result

After playing around a bit, I landed on the following:

Appending each row with a string containing a delimiter with JOIN(), then
Splitting them with SPLIT() and
Converting the resulting array into a column with TRANSPOSE()

Like this:

=TRANSPOSE(SPLIT(ArrayFormula(JOIN(" , , ,",FILTER(A:A, A:A <> ""))),","))

Google Sheets – Can I Cut and Paste Projects from a Google Sheet Using a Script?

Here is a script that does the following: if a user enters 'DONE' in any cell in sheet named 'Sheet1', the row with that cell is copied over to Sheet2, and deleted from Sheet1. To use, just enter in Tools > Script Editor, and change sheet names if needed.

The script does not check that 'DONE' is in the last nonempty cell of a row: it can be anywhere.

function onEdit(e) { 
  if (e.value === 'DONE') {
    var sheet = e.range.getSheet();
    if (sheet.getSheetName() == 'Sheet1') {    // Name of the sheet with projects
      logSheet = e.source.getSheetByName('Sheet2');   // Name of the archive sheet 
      var row = e.range.getRow(); 
      var lastRow = logSheet.getLastRow();
      var range = sheet.getRange(row, 1, 1, sheet.getLastColumn()); 
      range.copyTo(logSheet.getRange(lastRow + 1, 1));
      sheet.deleteRow(row);
    }
  }
}

Depending on the contents of your spreadsheet, you may want to replace the copying command with

range.copyTo(logSheet.getRange(lastRow + 1, 1), {contentsOnly: true});

so that only static values are copied, not formulas. This makes sense if the row contains formulas that would stop working properly if moved to another sheet.

Best Answer

Related Solutions

Google-sheets – How to add blank rows after each item from a FILTER() or QUERY() result

Google Sheets – Can I Cut and Paste Projects from a Google Sheet Using a Script?

Related Topic