Prerequisites
Approved robots where the Google Sheets integration is already enabled
Basic familiarity with Google Sheets formulas
How to identify failed tasks from a bulk run
Open your Google Sheet containing the bulk run results
Add a new column in robot B's sheet
Add the formula below to track failed tasks (you'll need to adjust the formula for your data)
Copy the formula down to all rows in the new column
=IFERROR(IF(INDEX(FILTER('robotA'!$A$2:$D, SEARCH(C2,'robotA'!$D$2:$D)), 1, 4) = C2, "Scraped", "Not Scraped"), "Not Scraped")The formula will show "Scraped" for successful tasks and "Not Scraped" for failed ones.
How to combine data from two robots
Open the Google Sheet containing robot A's data
Add a new column where you want the combined data
Add the formula below to bring in data from robot B (you'll need to adjust the formula for your data)
Copy the formula down to all rows in the new column
=INDEX(FILTER('robotB'!$A$2:$D, SEARCH(D2,'robotB'!$C$2:$C)), 1, 4)The formula will pull matching data from robot B into robot A's sheet.
Tips for success
Double check your sheet names match exactly in the formulas
Adjust column references (like $A$2:$D) based on your sheet layout
Test the formulas on a few rows before copying to all rows
Tutorial - Identifying failed tasks and combining data from ycombinator.com
In this example:
We've set up a bulk run on ycombinator.com to extract a list of companies (robot A), and the company detail (robot B).
After the bulk run is complete, the data was stored in separate sheets in Google Sheets via the Google Sheets integration—one for the list data (robot A -ycombinator A) and another for the detail data (robot B - ycombinator B).
Determining which tasks failed
After the integration, in the new column of the sheet for Robot B, utilize the following formula for the new column:
=IFERROR(IF(INDEX(FILTER('ycombinator A'!$A$2:$D, SEARCH(C2,'ycombinator A'!$D$2:$D)), 1, 4) = C2, "Scraped", "Not Scraped"), "Not Scraped")How does the formula work?
Utilizes the filter function to extract data from robot A (ycombinator A) within columns A to D.
It searches for the corresponding data in robot B (ycombinator B) column C2.
If the data is successfully scraped, it displays "Scraped"; otherwise, it shows "Not Scraped."
The formula also handles any errors that may occur during the process.
Bring the data from both robots into one Google Sheet
To bring the detailed data from robot B into the sheet of robot A, we can utilize the following formula:
=INDEX(FILTER('ycombinator B'!$A$2:$D, SEARCH(D2,'ycombinator B'!$C$2:$C)), 1, 4)How does the formula work?
Filters the data from "ycombinator B" based on the search term in column D of "ycombinator A"
The index function extracts the desired details and brings them into "ycombinator A".
You can repeat this process for other data points you want to consolidate, allowing you to access all the necessary information in one sheet.
