Automate Business Information Collection

Automate Business Information Collection with Google Sheets and Google Apps Script

In today’s fast-paced business environment, having quick access to detailed information about potential partners, clients, or competitors can give you a significant edge. Automating the process of gathering business information saves time and ensures accuracy. In this blog, I'll walk you through the process of creating a business information scraper using Google Sheets and Google Apps Script. This versatile tool can be adapted to gather data on any type of business, not just a specific industry. By following these steps, you'll be able to collect business names, addresses, phone numbers, email addresses, and websites efficiently.

Why Automate Data Collection?

Automating data collection helps in:

Reducing manual effort and errors.
Speeding up the data gathering process.
Ensuring consistent and up-to-date information.
Allowing you to focus on data analysis and decision-making.

Step-by-Step Guide to Creating a Business Information Scraper

Step 1: Set Up Your Google Sheet

Create a New Google Sheet
- Open Google Sheets and create a new spreadsheet.
- Label the columns as follows: Name, Phone Number, Address, Website, and Email.
Open Google Apps Script Editor
- Navigate to Extensions > Apps Script to open the script editor.

Step 2: Write the Google Apps Script

The script will query the Google Places API to fetch business details based on a search query. It will handle pagination to ensure all results are collected and avoid duplicates.

Create the Script
- Copy and paste the following script into the Apps Script editor, replacing 'YOUR_API_KEY_HERE' with your actual Google Places API key.

function searchBusinessesAndWriteToSheet() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var apiKey = 'YOUR_API_KEY_HERE'; // Replace with your actual API key

var baseQuery = 'businesses in '; // Base query for businesses

var cities = ['City1, State', 'City2, State', 'City3, State']; // List of cities to include in the search

var maxResults = 100;

var resultsFetched = 0;

var nextPageToken = '';

var cityIndex = 0;

// Retrieve existing data from the sheet

var existingData = sheet.getDataRange().getValues();

var existingNames = existingData.map(function(row) { return row[0]; }); // Assuming name is in the first column

function fetchAndProcess(url) {

Logger.log('Fetching URL: ' + url);

var response = UrlFetchApp.fetch(url);

var json = JSON.parse(response.getContentText());

var results = json.results;

nextPageToken = json.next_page_token;

Logger.log('Results length: ' + results.length);

Logger.log('Next page token: ' + nextPageToken);

if (results && results.length > 0) {

results.forEach(function(place) {

if (resultsFetched >= maxResults) return;

// Check if the place already exists

if (existingNames.includes(place.name)) {

Logger.log('Duplicate found: ' + place.name);

return;

}

// For each place, make an additional request to get its details

var detailsUrl = 'https://maps.googleapis.com/maps/api/place/details/json?place_id=' + place.place_id + '&fields=name,formatted_address,international_phone_number,website&key=' + apiKey;

Logger.log('Fetching details URL: ' + detailsUrl);

var detailsResponse = UrlFetchApp.fetch(detailsUrl);

var detailsJson = JSON.parse(detailsResponse.getContentText());

if (detailsJson.status === 'OK') {

var details = detailsJson.result;

var name = details.name || 'N/A';

var address = details.formatted_address || 'N/A';

var phone = details.international_phone_number || 'N/A';

var website = details.website || 'N/A';

// Email cannot be directly fetched from the API

var email = 'N/A'; // Placeholder

// Write to the next available row in the sheet

var lastRow = sheet.getLastRow();

var rowData = [name, phone, address, website, email];

Logger.log('Writing data: ' + JSON.stringify(rowData));

sheet.getRange(lastRow + 1, 1, 1, rowData.length).setValues([rowData]);

resultsFetched++;

// Add the new name to the existing names array

existingNames.push(name);

Logger.log('Fetched and added: ' + name);

} else {

Logger.log('Details fetch failed for: ' + place.name);

}

});

} else {

Logger.log('No results found for URL: ' + url);

}

Logger.log('Results fetched so far: ' + resultsFetched);

// Pause to allow for the next_page_token to become valid

if (nextPageToken) {

Logger.log('Next page token available, sleeping for 3 seconds...');

Utilities.sleep(3000); // 3 seconds

}

function performSearch() {

if (resultsFetched >= maxResults || cityIndex >= cities.length) {

Logger.log('Search complete. Total results fetched: ' + resultsFetched);

return;

}

var query = baseQuery + cities[cityIndex];

var responseUrl = 'https://maps.googleapis.com/maps/api/place/textsearch/json?query=' + encodeURIComponent(query) + '&key=' + apiKey;

fetchAndProcess(responseUrl);

while (resultsFetched < maxResults && nextPageToken) {

var nextPageUrl = 'https://maps.googleapis.com/maps/api/place/textsearch/json?pagetoken=' + nextPageToken + '&key=' + apiKey;

fetchAndProcess(nextPageUrl);

}

cityIndex++;

performSearch(); // Recursive call to move to the next city

}

performSearch();

}

Step 3: Execute the Script

Save the Script: Save your script in the Apps Script editor.
Run the Script: Click the run button (triangle icon) to execute the script.
Authorize the Script: If prompted, authorize the script to access your Google Sheets and make external API calls.
Check Your Sheet: After the script runs, check your Google Sheet to see the collected data.

Step 4: Adjust and Customize

Update the City List: Modify the cities array to include the locations you are interested in.
Change the Query: Adjust the baseQuery to search for different types of businesses (e.g., "restaurants in ", "doctors in ").
Increase Results Limit: Modify maxResults if you need more or fewer results.

Conclusion

By following these steps, you can create a powerful tool to automate the collection of business information using Google Sheets and Google Apps Script. This method can be adapted to gather data on various types of businesses, making it a versatile solution for many needs. Whether you’re looking to automate your workflow or showcase your technical prowess to potential clients, this guide provides a solid foundation.

Benefits of Automation

Efficiency: Automates repetitive tasks, freeing up your time for more strategic activities.
Accuracy: Reduces the risk of human error in data collection.
Scalability: Easily scalable to collect more data as your needs grow.

Future Enhancements

Email Collection: Integrate email scraping tools or APIs to collect email addresses.
Data Cleaning: Implement data cleaning functions to standardize and validate the collected data.
Visualization: Use Google Sheets' built-in charting tools or integrate with Google Data Studio for data visualization.

Why Work with me?

While this guide provides you with the tools to automate business data collection, setting up and running these scripts can be time-consuming and requires a bit of technical know-how. If you’d rather focus on your core business activities, we’re here to help. Our team can handle all aspects of data collection and automation for you, ensuring that you get accurate, up-to-date information without any hassle.

Feel free to reach out to us at [Your Contact Information] for a consultation or to discuss how we can tailor our solutions to fit your business needs. Let us help you streamline your data collection processes so you can concentrate on what you do best.

By leveraging the power of Google Apps Script and the Google Places API, you can create a robust business information scraper that serves various purposes and showcases your technical expertise. And remember, we're here to assist you in achieving your business goals efficiently and effectively. Reach out today to get started!