How to save web page changes in Google Sheets?

Raksha

Logo of Distill, Webhook and Google Sheet

Google sheet is a very popular and handy application used to store, analyze and process data. If you would to monitor and save web page changes in a Google spreadsheet, you can use Distill’s webhook actions after adding the webpage in your Watchlist.

Here are the steps that you can follow:

Step 1: Create spreadsheet web app to accept data from Distill

  1. Create a new Google Spreadsheet or open the spreadsheet where you want to get the changes on a web page.

  2. Name the spreadsheet and the sheet (default value is Sheet1). You can also add a header in the first row.

  3. From the spreadsheet’s menu bar click Extensions -> Apps Script editor to open the script editor. Rename the script from “Untitled project” to something suitable. (e.g. “DistillAlertToSheet”). Navigate to Apps Script

  4. Remove the current content from the script editor and replace with the following:

    function doPost(e) {
  var postJSON = e.postData.getDataAsString();
  var data = JSON.parse(postJSON);
  writeToSheet(data);
  if(typeof e !== 'undefined')
    return ContentService.createTextOutput(JSON.stringify(e.parameter));
}

function writeToSheet(params) {
  var ss = SpreadsheetApp.openById("<GOOGLE_SHEET_ID>");
  var ws = ss.getSheetByName("<SHEET_NAME>");
  // format time in PST. change it to use your own timezone
  var ts = Utilities.formatDate(new Date(params.ts), 'PST', "MM/dd/yyyy HH:mm:ss");
  // following line saves three fields: timestamp, name, and text
  // it can be changed to save custom fields received from Distill
  ws.appendRow([ts, params.name, params.text]);
}

  5. Open the spreadsheet and copy its ID as shown below. It is between two forward slashes. Once copied, replace <GOOGLE_SHEET_ID> with the spreadsheet ID. Similarly replace the <SHEET_NAME> with your sheet’s name in the script and save.

  6. From the menu bar, click Deploy -> New deployment. This will open a configuration window. Deploy button

    • Click the cog icon and select Web app to change script’s type.
      option to deploy as a web app
    • In the access type, you can select Anyone.
    • Once configured, click on Deploy. option to access script by anyone

  7. You will be taken to autorization window. Click on Autorize Access and proceed. Choose the Google account and click Allow. Authorize Web App

  8. Note down the Web app URL as shown below and click Done. Copy Web App URL

Step 2: Send updated content from Distill to Google

Check out: Webhook actions on changes to learn more about how webhooks work in Distill.

Follow these steps to configure the webhook action to send data to the spreadsheet:

  1. Go to a monitor’s Options page in Distill’s Watchlist

  2. Add a Webhook Call action under Actions.

  3. Enter the URL in Webhook Call action.

    • You can create the webhook fields that you want to see in the spreadsheet. Click on the Options button under Webhook URL to view and edit the fields to be sent to the Web app created earlier. Enter Web App URL in Webhook

      By default id, name, uri and text fields are added. Add ts (timestamp when page change was found) with {{sieve_data.ts}} as the value.

  4. Save Options.

Now you are all set to see the updated web page content in your Google Sheet. As a test, you can add a test monitor for https://www.timeanddate.com/ and check out the data in your Google Sheet after adding the Webhook action.

Change history in Google Sheet