Google sheets hit picking - Revision history

Matthew O'Meara at 17:06, 5 October 2020

2020-10-05T17:06:08Z

← Older revision		Revision as of 17:06, 5 October 2020
Line 24:		Line 24:
	=IMAGE(CONCATENATE("http://zinc15.docking.org/substances/", B2, "-small.png"))		=IMAGE(CONCATENATE("http://zinc15.docking.org/substances/", B2, "-small.png"))

	<li>Use IMPORTDATA to populate additional columns of interest in the header of the next columns. It looks like it can only load 3 columns at a time, so for example put ZINC_Number in column B and to retrieve data from ZINC into columns E-H, make those columns empty an put into cell E1 the IMPORTDATA function call:		<li>Use IMPORTDATA to populate additional columns of interest in the header of the next columns. It looks like it can only load 3 columns at a time, so for example put ZINC_Number in column B and to retrieve data from ZINC into columns E-H, make those columns empty an put into cell E1 the IMPORTDATA function call</li>

	Put in cell E1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=zinc_id smiles mol_formula logp"))		Put in cell E1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=zinc_id smiles mol_formula logp"))

	Then to fill in I-K:		Then to fill in I-K:
Line 35:		Line 35:

	Put in cell L1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=predicted_gene_names purchasability supplier_codes"))		Put in cell L1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=predicted_gene_names purchasability supplier_codes"))
	~~</ol>~~

	To figure out what column Look up additional columns of interest here http://zinc15.docking.org/substances/help/. As a sanity check include the zinc_id in the output column and verify that it corresponds with the input column</li>		To figure out what column Look up additional columns of interest here http://zinc15.docking.org/substances/help/. As a sanity check include the zinc_id in the output column and verify that it corresponds with the input column</li>
			</ol>

Matthew O'Meara at 17:01, 5 October 2020

2020-10-05T17:01:23Z

← Older revision		Revision as of 17:01, 5 October 2020
Line 10:		Line 10:
	To Displaying substance 2D structure from ZINC_ID		To Displaying substance 2D structure from ZINC_ID
	<ol>		<ol>
	<li>'''Sort zinc_ids numerically'''. This is really important because ~~otherwise~~ the results won't match up with the right compound.		<li>'''Sort zinc_ids numerically'''. This is really important because ZINC returns the values sorted numerically and if they aren't pre-sorted results won't match up with the right compound.
	<ol>		<ol>
	<li>Put zinc_ids in a column, e.g. B2-B100'</li>		<li>Put zinc_ids in a column, e.g. B2-B100'</li>
Line 24:		Line 24:
	=IMAGE(CONCATENATE("http://zinc15.docking.org/substances/", B2, "-small.png"))		=IMAGE(CONCATENATE("http://zinc15.docking.org/substances/", B2, "-small.png"))

	<li>Use IMPORTDATA to populate additional columns of interest in the header of the next columns. It looks like it can only load 3 columns at a time, so for example put the ~~first~~ cell ~~D1, the next . Look up additional columns of interest here~~ http://zinc15.docking.org/substances~~/help/~~. ~~As a sanity check include the~~ zinc_id in ~~the output column and verify that it corresponds with the input column</li>~~		<li>Use IMPORTDATA to populate additional columns of interest in the header of the next columns. It looks like it can only load 3 columns at a time, so for example put ZINC_Number in column B and to retrieve data from ZINC into columns E-H, make those columns empty an put into cell E1 the IMPORTDATA function call:

			Put in cell E1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=zinc_id smiles mol_formula logp"))

			Then to fill in I-K:

	~~Put in cell E1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=zinc_id smiles mol_formula logp"))~~
	Put in cell I1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=mwt num_chiral_centers reactivity"))		Put in cell I1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=mwt num_chiral_centers reactivity"))

			And to fill in L-M:

	Put in cell L1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=predicted_gene_names purchasability supplier_codes"))		Put in cell L1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=predicted_gene_names purchasability supplier_codes"))
	</ol>		</ol>

			To figure out what column Look up additional columns of interest here http://zinc15.docking.org/substances/help/. As a sanity check include the zinc_id in the output column and verify that it corresponds with the input column</li>

Matthew O'Meara: add looking up structures by smiles

2020-01-28T21:39:27Z

add looking up structures by smiles

← Older revision		Revision as of 21:39, 28 January 2020
Line 1:		Line 1:
	To ~~use Google sheets for hit~~-~~picking~~		To display substance 2D structure by smiles
			<ol>
			<li> Put the smiles in column e.g. B2-B100 and then put this formula in the adjacent column</li>

			=IMAGE(CONCATENATE("http://zinc15.docking.org/apps/mol/draw.png?smiles=", ENCODEURL(B59)))

			<li> I sometimes have issues with the compounds loading, so it may be useful to save them once they load. Copy column and paste in adjacent column and select "by value" from the drop-down list</li>
			</ol>

			To Displaying substance 2D structure from ZINC_ID
	<ol>		<ol>
	<li>'''Sort zinc_ids numerically'''. This is really important because otherwise the results won't match up with the right compound.		<li>'''Sort zinc_ids numerically'''. This is really important because otherwise the results won't match up with the right compound.

Matthew O'Meara at 16:57, 13 June 2019

2019-06-13T16:57:58Z

← Older revision		Revision as of 16:57, 13 June 2019
Line 16:		Line 16:
	=IMAGE(CONCATENATE("http://zinc15.docking.org/substances/", B2, "-small.png"))		=IMAGE(CONCATENATE("http://zinc15.docking.org/substances/", B2, "-small.png"))

	<li>Use IMPORTDATA to populate additional columns of interest in the header of the next columns. It looks like it can only load 3 columns at a time, so for example put the first cell D1, the next . Look up additional columns of interest here `http://zinc15.docking.org/substances/help/`. As a sanity check include the zinc_id in the output column and verify that it corresponds with the input column</li>		<li>Use IMPORTDATA to populate additional columns of interest in the header of the next columns. It looks like it can only load 3 columns at a time, so for example put the first cell D1, the next . Look up additional columns of interest here http://zinc15.docking.org/substances/help/. As a sanity check include the zinc_id in the output column and verify that it corresponds with the input column</li>

	Put in cell E1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=zinc_id smiles mol_formula logp"))		Put in cell E1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=zinc_id smiles mol_formula logp"))

Matthew O'Meara at 17:44, 27 February 2019

2019-02-27T17:44:32Z

← Older revision		Revision as of 17:44, 27 February 2019
Line 1:		Line 1:
	To use Google sheets for hit-picking		To use Google sheets for hit-picking

	1. '''Sort zinc_ids numerically'''. This is really important because otherwise the results won't match up with the right compound.		<ol>
	a. Put zinc_ids in a column, e.g. B2-B100'		<li>'''Sort zinc_ids numerically'''. This is really important because otherwise the results won't match up with the right compound.
	b. If you try to sort them directly, it will sort them lexigraphically, eg. put ZINC100 before ZINC34. So extract the zinc number from the zinc id by making a column 'ZINC_Number" by putting in cell C2		<ol>
			<li>Put zinc_ids in a column, e.g. B2-B100'</li>
			<li>If you try to sort them directly, it will sort them lexigraphically, eg. put ZINC100 before ZINC34. So extract the zinc number from the zinc id by making a column 'ZINC_Number" by putting in cell C2</li>

	=VALUE(SUBSTITUTE(B2, "ZINC", ""))		=VALUE(SUBSTITUTE(B2, "ZINC", ""))

	c. sort sort the cells by the ZINC_Number column by selecting the whole sheet and then data --> sort range... --> ZINC_Number		<li>sort sort the cells by the ZINC_Number column by selecting the whole sheet and then data --> sort range... --> ZINC_Number</li>
			</li>
	2. To depict the molecules copy and paste the following in each cells in new column. E.g. put this in cell D2 to depict the molecule in B2		</ol>
			<li>To depict the molecules copy and paste the following in each cells in new column. E.g. put this in cell D2 to depict the molecule in B2</li>

	=IMAGE(CONCATENATE("http://zinc15.docking.org/substances/", B2, "-small.png"))		=IMAGE(CONCATENATE("http://zinc15.docking.org/substances/", B2, "-small.png"))

	3. Use IMPORTDATA to populate additional columns of interest in the header of the next columns. It looks like it can only load 3 columns at a time, so for example put the first cell D1, the next . Look up additional columns of interest here `http://zinc15.docking.org/substances/help/`. As a sanity check include the zinc_id in the output column and verify that it corresponds with the input column		<li>Use IMPORTDATA to populate additional columns of interest in the header of the next columns. It looks like it can only load 3 columns at a time, so for example put the first cell D1, the next . Look up additional columns of interest here `http://zinc15.docking.org/substances/help/`. As a sanity check include the zinc_id in the output column and verify that it corresponds with the input column</li>

	Put in cell E1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=zinc_id smiles mol_formula logp"))		Put in cell E1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=zinc_id smiles mol_formula logp"))
	Put in cell I1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=mwt num_chiral_centers reactivity"))		Put in cell I1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=mwt num_chiral_centers reactivity"))
	Put in cell L1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=predicted_gene_names purchasability supplier_codes"))		Put in cell L1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=predicted_gene_names purchasability supplier_codes"))
			</ol>

Matthew O'Meara at 17:38, 27 February 2019

2019-02-27T17:38:59Z

← Older revision		Revision as of 17:38, 27 February 2019
Line 5:		Line 5:
	b. If you try to sort them directly, it will sort them lexigraphically, eg. put ZINC100 before ZINC34. So extract the zinc number from the zinc id by making a column 'ZINC_Number" by putting in cell C2		b. If you try to sort them directly, it will sort them lexigraphically, eg. put ZINC100 before ZINC34. So extract the zinc number from the zinc id by making a column 'ZINC_Number" by putting in cell C2

	=VALUE(SUBSTITUTE(B2, 'ZINC', ''))		=VALUE(SUBSTITUTE(B2, "ZINC", ""))

	c. sort sort the cells by the ZINC_Number column by selecting the whole sheet and then data --> sort range... --> ZINC_Number		c. sort sort the cells by the ZINC_Number column by selecting the whole sheet and then data --> sort range... --> ZINC_Number

Matthew O'Meara: more details on how to sort by zinc number

2019-02-27T17:38:29Z

more details on how to sort by zinc number

← Older revision		Revision as of 17:38, 27 February 2019
Line 1:		Line 1:
	To use Google sheets for hit-picking		To use Google sheets for hit-picking

	1. '''Put zinc_ids ~~in sorted order~~ in a column, e.g. B2-B100'''		1. '''Sort zinc_ids numerically'''. This is really important because otherwise the results won't match up with the right compound.
			a. Put zinc_ids in a column, e.g. B2-B100'
			b. If you try to sort them directly, it will sort them lexigraphically, eg. put ZINC100 before ZINC34. So extract the zinc number from the zinc id by making a column 'ZINC_Number" by putting in cell C2

	2. To depict the molecules copy and paste the following in each cells in new column. E.g. put this in cell C2 to depict the molecule in B2		=VALUE(SUBSTITUTE(B2, 'ZINC', ''))

			c. sort sort the cells by the ZINC_Number column by selecting the whole sheet and then data --> sort range... --> ZINC_Number

			2. To depict the molecules copy and paste the following in each cells in new column. E.g. put this in cell D2 to depict the molecule in B2

	=IMAGE(CONCATENATE("http://zinc15.docking.org/substances/", B2, "-small.png"))		=IMAGE(CONCATENATE("http://zinc15.docking.org/substances/", B2, "-small.png"))

	3. Use IMPORTDATA to populate additional columns of interest in the header of the next columns. It looks like it can only load 3 columns at a time, so for example put the first cell D1, the next . Look up additional columns of interest here `http://zinc15.docking.org/substances/help/`		3. Use IMPORTDATA to populate additional columns of interest in the header of the next columns. It looks like it can only load 3 columns at a time, so for example put the first cell D1, the next . Look up additional columns of interest here `http://zinc15.docking.org/substances/help/`. As a sanity check include the zinc_id in the output column and verify that it corresponds with the input column

	Put in cell D1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=smiles mol_formula logp"))		Put in cell E1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=zinc_id smiles mol_formula logp"))
	Put in cell G1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=mwt num_chiral_centers reactivity"))		Put in cell I1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=mwt num_chiral_centers reactivity"))
	Put in cell K1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=predicted_gene_names purchasability supplier_codes"))		Put in cell L1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=predicted_gene_names purchasability supplier_codes"))

Matthew O'Meara at 17:01, 27 February 2019

2019-02-27T17:01:14Z

← Older revision		Revision as of 17:01, 27 February 2019
Line 1:		Line 1:
	To use Google sheets for hit-picking		To use Google sheets for hit-picking

	1. Put zinc_ids in sorted order in a column, e.g. B2-B100		1. '''Put zinc_ids in sorted order in a column, e.g. B2-B100'''

	2. To depict the molecules copy and paste the following in each cells in new column. E.g. put this in cell C2 to depict the molecule in B2		2. To depict the molecules copy and paste the following in each cells in new column. E.g. put this in cell C2 to depict the molecule in B2

Line 10:		Line 11:
	Put in cell D1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=smiles mol_formula logp"))		Put in cell D1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=smiles mol_formula logp"))
	Put in cell G1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=mwt num_chiral_centers reactivity"))		Put in cell G1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=mwt num_chiral_centers reactivity"))
	Put in cell K1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=predicted_gene_names supplier_codes"))		Put in cell K1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=predicted_gene_names purchasability supplier_codes"))

Matthew O'Meara: how to use google sheets for hit-picking

2018-12-17T17:49:16Z

how to use google sheets for hit-picking

New page

To use Google sheets for hit-picking

1. Put zinc_ids in sorted order in a column, e.g. B2-B100
2. To depict the molecules copy and paste the following in each cells in new column. E.g. put this in cell C2 to depict the molecule in B2

=IMAGE(CONCATENATE("http://zinc15.docking.org/substances/", B2, "-small.png"))

3. Use IMPORTDATA to populate additional columns of interest in the header of the next columns. It looks like it can only load 3 columns at a time, so for example put the first cell D1, the next . Look up additional columns of interest here `http://zinc15.docking.org/substances/help/`

Put in cell D1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=smiles mol_formula logp"))
Put in cell G1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=mwt num_chiral_centers reactivity"))
Put in cell K1: =IMPORTDATA(CONCATENATE("http://zinc15.docking.org/substances.csv?zinc_id-in=", JOIN("+", B2:B100),"&output_fields=predicted_gene_names supplier_codes"))