How can i check a list of websites on google sheet for safe browsing periodically

Help
1

Hello Everyone,
I wonder if there is a way to periodically check a list of websites listed on google sheets if they are safe for browsing using google safebrowsing checker and report it back to the same sheet with a status of the website if its clean or its a threat ?
it works with new row added trigger, but i will have say 10 website URLs and then the checker check them all at once every 1 hour and write back to the sheet each one if clean or is a threat ?

Thanks

2

Hi @alostura.2023,

Yes, you can create a Pipedream workflow to periodically check a list of websites listed on Google Sheets using Google Safe Browsing and update the sheet with the status. Here’s a high-level overview of the workflow:

  1. Use a Scheduler trigger to run the workflow every hour.
  2. Read the URLs from the Google Sheet.
  3. For each URL, check if it’s safe using Google Safe Browsing API.
  4. Update the Google Sheet with the status of each website.

To get started, create a new workflow and add a Scheduler trigger to run every hour. Then, you can use the following code as an example to read URLs from the Google Sheet, check their safety, and update the sheet:

import { axios } from "@pipedream/platform";

export default defineComponent({
  props: {
    google: {
      type: "app",
      app: "google",
    },
    sheetId: {
      type: "string",
      label: "Google Sheet ID",
    },
    apiKey: {
      type: "string",
      label: "Google Safe Browsing API Key",
    },
  },
  async run({ $ }) {
    // Read URLs from Google Sheet
    const sheetData = await axios($, {
      url: `https://sheets.googleapis.com/v4/spreadsheets/${this.sheetId}/values/A1:A10`,
      headers: {
        Authorization: `Bearer ${this.google.$auth.oauth_access_token}`,
      },
    });

    const urls = sheetData.values;

    // Check URLs using Google Safe Browsing API
    const safeBrowsingResults = await axios($, {
      method: "POST",
      url: `https://safebrowsing.googleapis.com/v4/threatMatches:find?key=${this.apiKey}`,
      data: {
        client: {
          clientId: "pipedream",
          clientVersion: "1.0",
        },
        threatInfo: {
          threatTypes: ["MALWARE", "SOCIAL_ENGINEERING"],
          platformTypes: ["ANY_PLATFORM"],
          threatEntryTypes: ["URL"],
          threatEntries: urls.map((url) => ({ url })),
        },
      },
    });

    // Update Google Sheet with the status of each website
    const updateData = urls.map((url, index) => {
      const threat = safeBrowsingResults.data.matches.find(
        (match) => match.threat.url === url
      );
      const status 
  I’m a bot powered by Pipedream and GPT-4. I’m still learning, so please double check my answers and code! [Learn how to build your own.](https://pipedream.com/blog/using-the-new-openai-actions-in-pipedream/)
3

Thanks, I am new here but where should i add this code?

4

What you can do is

  1. Add a trigger on New Row Added
  2. Make a request to google safebrowsing checker with your url.
  3. Update you google sheet row with the url status (safe/unsafe)