How to easily find company domain URL based off company name in Google Sheets
HTML-код
- Опубликовано: 11 сен 2024
- Step 1: add (autocomplete.c...) to the right of the company name
Step 2: add (=CONCENTRATE) and combine the URL from step 1 with the company name. Example is: autocomplete.c...
Step 3: Tap on "Tools" then "Script Editor"
Step 4: Enter in this script and then save it...
function FETCH(url) {
var response = UrlFetchApp.fetch(url);
Logger.log(response.getContentText());
var out = JSON.parse(response.getContentText());
return out[0]['domain'];
Step 5: In a cell to the right, enter in =FETCH(URL)
Example: =FETCH(autocomplete.c...)
That's it! Weeee!
Sponsored by Hifive (hf.app) - the relationship-led growth platform for founders and go-to-market professionals.
works very fine for me, including names with spaces (600 urls retrieved)... it won't work when the company name does not match the real company name (ie, commercial name versus registred name, this kind of things) ... great tutorial anyway
Thank you for taking the time out of your day to post this and help other people out 💯
You got it! Glad you like it :)
It works like a charm. Thank you so much for sharing this.
How to get the company's address, phone number listed on Google?
doesn't work. my error says
TypeError: Cannot read properties of undefined (reading 'domain') (line 5).
Same here for me!
it is only missing a = after query
As others have pointed out, the function you are using is called CONCATENATE and not CONCENTRATE. Just if anybody else should wonder why they don't have that function.
But great video. Thanks for sharing! :)
This was such a helpful video! I love learning tricks/tips as an emerging IT professional.
Amazing!
Why do you say concentrate and then you write concatenate?
Because I can't spell
realy need help brother ....
#ERROR!
Thanks, mate! Works perfectly.
Amazing!
Hi,
I tried to run the code in script editor but I got an error. According to the debug feature, it was in the second line
Thanks for the heads up - we'll have to fix this.
Thanks, still up
Don't forget to write "=Concatenate" and to save your code
Test on famous company
Thank you for the video, however i am getting an error message (Syntax error: SyntaxError: Unexpected end of input line: 5 file: Code.gs)
I added a ) at the end, that didn't work. Then i deleted that and added a } at the end, but still the same results. Is there a way to solve this?
Maybe important to note also: i do not have the option to choose "Script Editor" under the Tools section.
Thanks again!
Running into this too!!
it's the "}" sign and not ")" that's missing 😀@@meguercia
function FETCH(url) {
var response = UrlFetchApp.fetch(url);
Logger.log(response.getContentText());
var out = JSON.parse(response.getContentText());
return out[0]["domain"];
}
@@meguercia
Try this revised/improved code:
function FETCH(url) {
try {
var response = UrlFetchApp.fetch(url);
var content = response.getContentText();
Logger.log(content);
var out = JSON.parse(content);
if (out.length > 0 && 'domain' in out[0]) {
return out[0]['domain'];
} else {
Logger.log("The response does not contain the expected format or 'domain' property.");
return null; // or any default value you deem appropriate
}
} catch (e) {
Logger.log("Error fetching URL or parsing response: " + e.toString());
return null; // or any error indication as per your need
}
}
Notes:
This version adds a try-catch block to handle potential exceptions from UrlFetchApp.fetch or JSON.parse.
It checks if the output array has at least one element and if the 'domain' property exists before trying to access it, which prevents runtime errors if the data is in an unexpected format.
If there's an issue, it logs the error and returns null. You might adjust this to better fit your error handling strategy.
Ensure you're calling the FETCH function correctly in your script and that the URL you're passing to it returns the data in the expected JSON array format.
Anyone else not see FETCH as a function in their Sheets?
Yes I can not copy paste the fetch function
Thanks for the video. However, I'm unable to find the script editor option in my account. Is this available only for Google Workplace accounts?
Use instead: Extensions>App Script
its not concentrate its concatenate which means to link together
when the company name has spaces, it doesn't work.
Yeah, that's because there is something called "URL-Encoding." Whitespaces for example have the URL-Encoding "%20" which is a hexadecimal number and in decimal is 32. If you look up the ASCII table, you will see, that it corresponds to the Whitespace Character. Similar things hold for other special symbols, that are not allowed as a URL-term. E.g. "&" encodes to "%26". That's why videos like these should not exist, they do not explain any background behind using an API or constructing URLs with company names.
Its very useful and how can I go for it get email id's
Thank you a lot!
You got it!
its concatenate
some companies and domains are not matching
Obviously, since there can be several companies with the same names and it will not for sure be known to which they actually map, since this is a tedious process of cross-validating and web-scraping for data analysis. There is as of today no publicly available solution, that does this in a perfect manner because there are so many things to check starting with all the different legal descriptions of companies.
good , but code editor , in 2024 exist in extensions , not in tools , thak you ,
Not sure what you mean, my friend.
@@hfapp He means in the menu bar. Also I am getting the same syntax error. How do i resolve that please? Thanks!
sir how get a domain to company address (full address) with zip
That is much more involved (programming and web-scraping) and must be done either by studied tech-guys (IT) or can be bought as an API-service. Alternatively you can look up the domain manually if you do not have the ability to somehow automate it.
this is not working
Try this revised/improved code:
function FETCH(url) {
try {
var response = UrlFetchApp.fetch(url);
var content = response.getContentText();
Logger.log(content);
var out = JSON.parse(content);
if (out.length > 0 && 'domain' in out[0]) {
return out[0]['domain'];
} else {
Logger.log("The response does not contain the expected format or 'domain' property.");
return null; // or any default value you deem appropriate
}
} catch (e) {
Logger.log("Error fetching URL or parsing response: " + e.toString());
return null; // or any error indication as per your need
}
}
Notes:
This version adds a try-catch block to handle potential exceptions from UrlFetchApp.fetch or JSON.parse.
It checks if the output array has at least one element and if the 'domain' property exists before trying to access it, which prevents runtime errors if the data is in an unexpected format.
If there's an issue, it logs the error and returns null. You might adjust this to better fit your error handling strategy.
Ensure you're calling the FETCH function correctly in your script and that the URL you're passing to it returns the data in the expected JSON array format.
@@MichaelLyons This has worked. Thanks!
CON-CAT-EH-NATE
Ladies and Gentleman, this video is probably the perfect showcase of script kiddie work at the lowest stages or even worse. 1. It is called "CONCATENATE" which stands for putting strings (character arrays) together. 2. You use excel to adjust the query url with the company name instead of using one and the same string with python and list of company names and dynamically (format string) putting company name in that url. 3. You manually change to concatenate (NOT CONCENTRATE) in every instance of your copied URI string, instead of renaming it once and copying it to all others again. 4. I feel sorry for you, because this is so much unnecessary work and I would strongly urge YOU and the others, who are watching this video to learn python/coding. Start with exactly this example and learn what a string, format string and a for loop is and how you can dynamically change that URL string to have company name in it. Absolutely atrocious, sorry to say but this video is misleading people into practices which they should never follow. Productivity here goes down the drain.
You do have a point that it would serve them well to learn programming concepts, but you probably could be nicer. If we're to be nice to beginner coders, then we should be nicer to those who aren't beginners yet but are already on the borders. And he did figure out how to call an API that returned results to his Excel sheet, in under 4 minutes. He's doing pretty well.
@@shiewhun1772 I think, for his line of work you can expect him to be able to figure that out, else there is something very wrong with which people occupy job positions that they are probably not capable of doing in terms of required analytical ability, efficiency and knowledge and thus should NOT occupy. That's why companies today have inflated numbers of consultants, salesperson, HR, etc. You get the gist. If the marco- and micro-economy studies at Unis/Higher Schools would be remotely demanding it would reduce the need for unnecessary communication/solving problems/waiting time and proper wage distribution by a large margin. But for some reason, people who studied economics in this day and age are allowed to be less competent in their job than people who have a different degree (that is: rigorous bachelor of arts or science/technology). This really chews on our productivity as a society and leaves companies in a state of more self-production rather than actual effectiveness and competence. Start-Ups are not allowed to have these conditions, that's why so many fail. But once a company is established, it typically attracts people, that think it a save harbour while not really contributing much to it and letting the tech-guys do the heavy work, going as as to command them to communicate with a customer, because they know, the tech-guy will appear more competent and will probably know as much or more about the companies product/service and how to present it as/than the customer service guy/consultant. This is pitiful and I have first-hand experienced it. Embarassing at best. No politeness needed.
@manojkumarhiremath8675 defuq... what?
Returned code 412
Top 5 autistic comment
Trash
Getting the following error: Syntax error: SyntaxError: Unexpected end of input line: 5 file: Code.gs
same