Convert columns of ISO 8601 strings to dates in Google Sheets

I’m pulling data to my sheet from an API using an ImportJSON function. The data array contains mixed data, including several columns of ISO 8601 date strings in UTC. For example:

2020-06-02T00:00:00Z
2020-06-02T03:00:00Z
2020-06-02T14:30:00Z

Google Sheets doesn’t apparently recognize these as dates, or at least applying a date format to these columns doesn’t do anything. Can I wrap a formula around the ImportJSON function that would parse certain columns to format the date and time properly?

google sheets – How to change cell format on ImportHTML Function

I’ve used the ImportHTML function to scrape play-by-play information from Basketball Reference.

I am using a Vlookup to create a rating system based on time, point different, and several other factors.

I realized the TIME vlookup isn’t reading correctly, but the formula is exactly how it should be (I’ve tested it, and it works fine when I type in the info manually). It only doesn’t work on the time that is coming from my ImportHTML.

I can’t get the format to match – I’ve tried every format option on both my vlookup & the data, and tried forcing it with the TEXT function (might of not done that correctly).

I’m at an absolute stand-still at this point — has anyone experienced anything similar?

Other ways to force a format change on imported data? or another way to correctly pull the values from a range of times? I’ve tried the Lookup function as well.

For Reference:

=VLOOKUP(H11,$J$2:$L$6,3,TRUE)

upper lower bounds

importfunction

Google Sheets value on chart off from source by very small fraction

Trying to figure out why Google Sheets chart is off from the source data by a small fraction. My sheet has 7 rows of data and a sum row. All data only has 2 decimal points and the sum obviously has 2 decimal points. Yet when plotted, the sum is off as shown in the screenshot below.

Screenshot

The callout on the chart is pulling its data from cell C12. Formula for C12 is just SUM(C4:C10). I expanded the decimal places in C12 to show that nothing was hidden/rounded/truncated. Trying to understand why this is happening and how to stop it.

Custom function in google sheets, that sees URL (web-link)

I am trying to write a custom function in google sheets.

I am trying to create a function to joint the content of two cells. It works for text, however it does not work if the text contains a web-link (URL). The link is removed.

The code

function combine(...theArgs) {
  var result ="";
  var is_first = true;
  theArgs.forEach( function (value, index){
    if (! is_first) {
      result += "nn";
    }
    is_first=false;
    result += value;
  });
  return result;
}

Usage =combine(ref, ref) e.g. =combine(a1,b1)

Acceptable answers: include how to get this function to work, and “Why are you using a custom function, why not use the built in function …?”

google sheets – Find the last instance of multiple values

I really need some help with this.

I need to search for the value of A2:B2 account1(A2):site1(B2) in another tab in google sheets and it needs to return the row number of last occurrence/entry of the values in A2:B2 in Column C2.

Tried multiple formulas and I couldn’t seem to make it work.

Recommendations and advice will be truly appreciated. And thank you in advance as well.

google sheets – Conditional Formatting w/multiple conditions and partial text match

I am working in google sheets and have a manual input quantity in column E. I am trying to write a conditional format that will highlight each cell in column E (or the whole row) that is greater than 1 only if column C’s matching row begins with “EM”. I have a few variations but this is closest that could seem to work but cells that do not match the conditions are getting highlighted and cells with the conditions are being omitted…

=and(regexmatch(C2,”EM”),E2>1)

I know I will need some wildcards in there but I tried a few different combo’s already with no luck. Thanks for any help!

google sheets – How does one automatically generate a hyperlink for a cell containing a path?

I’m familiar with the HYPERLINK formula in Google Sheets to create links in a column, using a value in separate column as seen in this screenshot in row #2.

enter image description here

I want the user to have the ability to enter a path and have a link, using a predefined domain, automatically generated, as shown in A3, without having to use the “INSERT” -> “Insert link” command. This way if they insert new rows, and add a path to a new cell, the link will automatically generate. And if they update the path in the cell, the link updates accordingly. I’m hoping to set this up to also save space because an extra column doesn’t have to be added to the sheet.

I tried conditional formatting, but wasn’t able to accomplish my goal.

how can I create a google sheets script to generate hyperlinks to corresponding tabs based on cell value?

I have a list and want to quickly add a hyperlink to the corresponding tab.
Say my list is in column BC and looks like:

A

B

C

A

C

B

I would like all the A’s to have a hyperlink to the sheet within the same google sheet to go to the sheet labeled “A”.

It is quite a long list and would like to save time rather than hyperlinking each cell at a time by running a script of some sort.
Thanks!

Citation Power Ups using sheets for $5

Citation Power Ups using sheets

Citation Power Ups

Using Google drive file sheets for backlinks is powerful and safe.

Building backlinks to your citations has a good SEO Value. They contain your Name,Address,and Phone (NAP) and

sometimes other important info about your online business and G oogle

understands this. Power ups will help your overall brand/entity.

Basic Plan $5

6 kw x 10 URLs

Will give you 60 links per sheet

6 KW named folders (folders help)

360 links total from Google.(36 links for each url total)

Extras

Standard Plan

$2 more

6 KW x 15 urls will give you 90 links per sheet

90 urls per sheet x 6 KW folders=540 total links (36 links for each URL)

Premium Plan

$4 more

6 KW x 20 urls will give you 120 links per sheet

120 links per sheet x 6 KW folders=720 total links (36 links for each URL)

230 embeds $5 -we take the sheet iframes and embed them into 230 niche related web2.0 to help juice

them up more and give you the iframes codes that you can use anywhere

that embeds are allowed.

Citations are very relevant to your local biz so why not?

I would suggest using:

keyword+branding

and/or

keyword+location

What do I need?

You need to supply x amount of KWs and xx amount of URLs, depending on which package you buy. These can also be

your socials, your web 2.0s,whatever you want to add more juice to.

Questions???-What is KW+branding?

roofing BullyServices.com

roofer BullyServices

roofing Contractor Bully Services

-Will my citation sites show up on page one of search?

Maybe maybe not thats not the point of this gig, but to help improve your business overall online presence.

Whats a citation?

Yellowpages, Yelp, Manta,ETC but can also include your main social accts

(youtube’s about page would be good here,if filled out).

If these are safe why can I not use for my money site?

Your money site is Top Level Domain (TLD) and is a bit more fragile than

a citation site,social site or web 2.0s as they have thousands of links

pointed to them, your domain name does not and could not handle the

sudden amount of links being built to it. You dont want to go there with

your TLD.

Read this before buying

-I would suggest not to use your MS-website

-No sites about drugs,pills,gambling,adult,CBD,psychic,headshops,etc if your not sure ask before order.

-We are selling backlinks not rankings.

.(tagsToTranslate)citation(t)powerups(t)sheets(t)drivefil(t)local(t)seo

google sheets – Filter for a QUERY function

You would just use and

The only column containing a different variable is column B.
So you could use

=query(Sheet1!A:F, "SELECT A, B, C, D, E, F where E contains 'Apple' and B contains 'had'")

Note:
you do not need the parentheses around E contains 'Apple'