How to send cell data from one google spreadsheet to another, but only if the cell colour is correct?

0
votes

I have a spreadsheet with cells coloured in two different colours. I know I can send all the cell data from one google spreadsheet to another using IMPORTRANGE function. However, I only want to send the cell data if it satisfies a specified cell colour.

For example, if spreadsheet A has 10x10 data with various colours, then spreadsheet B should contain all the data from cells in spreadsheet A that are either red or green (and also transfer the cell colours). All other cells with different colours from spreadsheet A should be transferred to spreadsheet B as blank colourless cells. The resulting spreadsheet should still contain 10x10 cell data, but with only red, green and blank cells.

I know it should be possible to write a function for this, but I have never written any custom functions before and have no Javascript experience. Any kind of help would be appreciated. Perhaps also the QUERY function could be of use?

Thanks in advance!

2
google-apps-scriptgoogle-sheetsgoogle-query-language

2 Answers

1
votes

You should check about Google Apps Script. It gives you a set of tools that will allow you to create a script for doing what you want.

Custom Functions will help you to create a function that lets you get the values from your sheet and then set the conditions you are requiring.

The Class SpreadsheetApp has the tools for handling all data in your sheets. Check for example the method getBackgrounds(), which gets the color in a range of cells.

This another post, it is a little similar in some aspects to what you want to do.

1
votes

It's best practice to create an additional column which stores the information regarding as to which condition (color) is applied 5o the particular row. Once you have done that, you can easily transport a table from one Spreadsheet to another using the QUERY formula within the IMPORTRANGE.

Image column a is the name of a city. Imagine column b holds the information regarding the condition (color). This is an helper column. Now we have col1 = New York City, col2= green

Then you could enter this into the new sheet. QUERY( IMPORTRANGE(URL, range), "SELECT col1 WHERE col2="green" OR col2="yellow" OR col2="red")

Here is a great tutorial series I like to use. https://youtu.be/_N5zhAipVn0