How to integrate Ambee Webhooks with Google sheets

AppScript

/*Post.gs Code for Webhooks*/
function doPost(e) {
  const lock = LockService.getScriptLock();
  try {
    lock.waitLock(28000);
  } catch (e) {
    response = {
      status: 'error',
      message: 'Request throttled'
    }
    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
  }
  let { parameters, postData: { contents, type } = {} } = e;
  let response = {};
  if (type === 'text/plain' || type === 'text/html' || type === 'application/xml') {
    response = {
      status: 'error',
      message: `Unsupported data-type: ${type}`
    }
    lock.releaseLock();
    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
  }
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = activeSpreadsheet.getSheets();
  const activeSheetsAndNewParams = gidHandlerForPost(parameters, activeSpreadsheet, allSheets);
  const activeSheets = activeSheetsAndNewParams.activeSheetNames;
  parameters = activeSheetsAndNewParams.revisedParameters;
  let keys = [];
  if (type === 'application/json' || (type === '' && contents.length > 0)) {
    let jsonData;
    try {
      jsonData = JSON.parse(contents);
    } catch (e) {
      response = {
        status: 'error',
        message: 'Invalid JSON format'
      };
      return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
    };
    jsonData = Array.isArray(jsonData) ? jsonData.map(data => flatten(data)) : [flatten(jsonData)];
    keys = Array.isArray(jsonData) ? ((jsonData[0].constructor === Object || jsonData[0].constructor === Array) ? Object.keys(jsonData[0]) : jsonData[0]) : Object.keys(jsonData);
    if (keys.length > 0) {
      activeSheets.forEach(activeSheetName => {
        let activeSheet = activeSpreadsheet.getSheetByName(activeSheetName);
        let headers = activeSheet.getDataRange().offset(0, 0, 1).getValues()[0];
        if (headers.length 0 || (headers.length 1 && headers[0].length == 0)) {
          activeSheet.appendRow(keys);
          activeSheet.setFrozenRows(1);
          if (logTimeStamp === true) {
            activeSheet.insertColumnBefore(1);
            SpreadsheetApp.flush();
            activeSheet.getRange("A1").setValue("timestamp_incoming_webhook");
            activeSheet.getRange("A:A").setNumberFormat('dd/MM/yyyy HH:mm:ss');
            SpreadsheetApp.flush();
            headers = activeSheet.getDataRange().offset(0, 0, 1).getValues()[0];
          } else {
            headers = keys;
          }
        }
        let rowData = [];
        const now = new Date();
        jsonData.forEach(rowLevelData => [rowLevelData].map(row => rowData.push(headers.map(key => key === "timestamp_incoming_webhook" ? now : row[String(key)] || ''))));
        activeSheet.getRange(activeSheet.getLastRow() + 1, 1, rowData.length, rowData[0].length).setValues(rowData);
      });
      response = {
        status: 'success',
        message: 'Data logged successfully'
      };
      lock.releaseLock();
      return ok200Status === true ?
        HtmlService.createHtmlOutput('Data logged successfully').setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL) :
        ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
    } else {
      response = {
        status: 'success',
        message: 'No parameters detected'
      };
      lock.releaseLock();
      return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
    }
  } else {
    if (parameters) {
      keys = Object.keys(parameters);
      if (keys.length > 0) {
        logTimeStamp === true ? parameters["timestamp_incoming_webhook"] = [new Date()] : null;
        keys = Object.keys(parameters);
        const cartesianData = cartesian(parameters);
        activeSheets.forEach(activeSheetName => {
          let activeSheet = activeSpreadsheet.getSheetByName(activeSheetName);
          let headers = activeSheet.getDataRange().offset(0, 0, 1).getValues()[0];
          if (headers.length 0 || (headers.length 1 && headers[0].length == 0)) {
            activeSheet.appendRow(keys);
            activeSheet.setFrozenRows(1);
            if (logTimeStamp === true) {
              activeSheet.moveColumns(activeSheet.getRange(1, keys.indexOf("timestamp_incoming_webhook") + 1), 1);
              SpreadsheetApp.flush();
              activeSheet.getRange("A:A").setNumberFormat('dd/MM/yyyy HH:mm:ss');
              headers = activeSheet.getDataRange().offset(0, 0, 1).getValues()[0];
            } else {
              headers = keys;
            }
          }
          let rowData = [];
          cartesianData.forEach(rowLevelData => [rowLevelData].map(row => rowData.push(headers.map(key => row[String(key)] || ''))));
          activeSheet.getRange(activeSheet.getLastRow() + 1, 1, rowData.length, rowData[0].length).setValues(rowData);
        });
        response = {
          status: 'success',
          message: 'Data logged successfully'
        };
        lock.releaseLock();
        return ok200Status === true ?
          HtmlService.createHtmlOutput('Data logged successfully').setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL) :
          ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
      } else {
        response = {
          status: 'success',
          message: 'No parameters detected'
        };
        lock.releaseLock();
        return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
      }
    }
  }
}
function gidHandlerForPost(params, activeSpreadsheet, allSheets) {
  let existingSheetIds = [];
  let postDefaultSheet;
  let newParameters = {};
  allSheets.forEach(sheet => existingSheetIds.push(sheet.getSheetId().toString()));
  let defaultWebhookPostSheetId = documentProperties.getProperty('defaultWebhookPostSheetId');
  let newDefaultWebhookPostSheetName = `[POST] Webhook — ${new Date().getTime().toString()}`;
  let checkDefaultOrCreateNewPostSheet = false;
  let keys = Object.keys(params);
  if (keys.includes('gid')) {
    const gidValues = params['gid'];
    const matchingGids = existingSheetIds.filter(sheetId => gidValues.includes(sheetId));
    const nonMatchingGids = gidValues.filter(gid => !matchingGids.includes(gid));
    if (matchingGids.length === 0) {
      checkDefaultOrCreateNewPostSheet = true;
    } else {
      newParameters = params;
      delete newParameters["gid"];
      if (nonMatchingGids.length > 0) {
        newParameters["gid"] = nonMatchingGids;
      }
      if (matchingGids.length === 1) {
        postDefaultSheet = allSheets.filter(sheet => sheet.getSheetId() == matchingGids[0]);
        return {
          activeSheetNames: [postDefaultSheet[0].getSheetName()],
          revisedParameters: newParameters,
        };
      } else {
        let validSheetNames = [];
        matchingGids.forEach(gid => {
          postDefaultSheet = allSheets.filter(sheet => sheet.getSheetId() == gid);
          if (postDefaultSheet.length !== 0) {
            validSheetNames.push(postDefaultSheet[0].getSheetName())
          }
        });
        return {
          activeSheetNames: validSheetNames,
          revisedParameters: newParameters,
        }
      }
    }
  } else {
    checkDefaultOrCreateNewPostSheet = true;
  }
  if (checkDefaultOrCreateNewPostSheet) {
    if (!defaultWebhookPostSheetId) {
      defaultWebhookPostSheetId = activeSpreadsheet.insertSheet().setName(newDefaultWebhookPostSheetName).getSheetId().toString();
      documentProperties.setProperty('defaultWebhookPostSheetId', defaultWebhookPostSheetId);
      return {
        activeSheetNames: [newDefaultWebhookPostSheetName],
        revisedParameters: params,
      };
    } else {
      postDefaultSheet = allSheets.filter(sheet => sheet.getSheetId() == defaultWebhookPostSheetId);
      if (postDefaultSheet.length !== 0) {
        return {
          activeSheetNames: [postDefaultSheet[0].getSheetName()],
          revisedParameters: params,
        };
      } else {
        defaultWebhookPostSheetId = activeSpreadsheet.insertSheet().setName(newDefaultWebhookPostSheetName).getSheetId().toString();
        documentProperties.setProperty('defaultWebhookPostSheetId', defaultWebhookPostSheetId);
        return {
          activeSheetNames: [newDefaultWebhookPostSheetName],
          revisedParameters: params,
        };
      }
    }
  }
}
const flatten = (obj, prefix = '', res = {}) =>
  Object.entries(obj).reduce((r, [key, val]) => {
    const k = `${prefix}${key}`;
    if (typeof val === 'object' && val !== null) {
      flatten(val, `${k}_`, r);
    } else {
      res[k] = val;
    }
    return r;
  }, res);

In today's data-driven world, having an efficient and streamlined data analysis workflow is crucial for businesses to make informed decisions. With the integration of Ambee Webhooks and Google Sheets, you can take your data analysis capabilities to the next level. This powerful combination allows you to seamlessly transfer and manipulate data from Ambee's comprehensive environmental API directly into your Google Sheets, enhancing the efficiency and effectiveness of your analytical processes.

Ambee webhooks: Enhance your data analysis through Google sheets integration

Ambee’s Webhooks alerts provide real-time access to a vast array of environmental data points such as air quality index, weather conditions, pollen levels, and more. By integrating this valuable information with Google Sheets - a widely used cloud-based spreadsheet platform - you can easily organize and analyze the data within familiar interfaces.

The benefits of this integration are:

Hassle-free data analysis: Effortlessly pull live air quality index readings from Ambee's API into designated columns in your Google Sheet and then create formulas or perform calculations on this data. Whether you are tracking trends over time or conducting complex analyses using multiple variables, this integration empowers you to make well-informed decisions based on up-to-date information.‍
Automated environmental data at your fingertips: Furthermore, with Ambee Webhooks pushing updates directly into your Google Sheet in real time, you have a continuous flow of fresh environmental data at your fingertips. This feature proves especially valuable for businesses that require immediate access to live information for critical decision-making processes.‍
Enhanced accuracy: Ambee webhooks integration with Sheets enhances accuracy by automating data retrieval and minimizing human intervention. This minimizes the risk of manual errors in collecting environmental information, ensuring precise and reliable data for various applications.
‍Saved Timed: Save time by eliminating manual data entry and the hassle of importing/exporting files between systems, streamlining data integration for increased efficiency.

Learn more about Ambee Webhooks and its capabilities: Ambee Webhooks: Empowering Insights through Push-Based Environmental Alerts

With seamless access to real-time environmental alerts combined with easy-to-use spreadsheet functionalities, you can elevate your analytical capabilities like never before. So why settle for manual data entry and outdated information when you can effortlessly enhance your data analysis workflow with this powerful integration?

For this reason, here’s a step-by-step guide on how to integrate Ambee Webhooks with Google Sheets.

Step-by-step guide to integrating Ambee webhooks with Google sheets

Integration of Google Sheets with the Ambee Webhooks Platform is quite simple. There are a total of 2 sections where the user has to configure details to make the integration successful.

Google Sheet Section:

First, the user has to create a Google Account, open the Google Sheets, and name it something like the one shown in the image below.

Second, the user needs to go to Extensions from the Sheets and select Apps Script. This will open an untitled project where you can write a script for the incoming webhooks data.

‍

‍

The purpose of this script is to fetch and dump Ambee data when the triggering conditions are met for further processing. Below is the script to do the fetch and dump work:

The next step is to simply copy and paste the code above to the Apps Script editor and then save it.

Once it’s saved, the next step is to deploy the app on the server, for which the steps are given through the images below.

Once the Web App is published, the user has to copy the URL provided under the Web App section.

Ambee Webhooks section

To set up an Ambee Webhooks account, simply select Webhooks from under the Product Tab on the Ambee website: https://www.getambee.com/.

Next, select Access Ambee Webhooks on the landing page and click on the Sign-up button. A pop-up will then ask you to Contact Us and get access to the Ambee Webhooks Platform.

Once you get access to the platform, your home screen will look something like this:

Click on the Add Integration button in the top right corner, and you will get an option to set up the alerts.

Here, you can enter details like Alert Name, Data Type, Locations, and Conditions to trigger the alerts.

Now, you have to enter the URL you copied from Google Sheets. Then, save all the details and go to the last step shown below.

You must now enter the frequency and time range as per your requirements and click Submit.

Now you are all set to receive the alerts as per your setup triggering condition.

To visualize the data on Google Sheets, you have to wait for an hour. If your frequency is set to hourly, the data on your sheet will look like this:

Similarly, you can set up alerts for your desired data sets, such as weather, pollen, or air quality, by providing various triggering conditions.

Use cases for Ambee Webhooks’ integration with Google sheets

Accurate data analysis

Use webhooks to receive real-time environmental data updates from Ambee's API. Analyze the data to track trends, identify patterns, and gain insights into changing environmental conditions.

Environmental data integration

Integrate Ambee's environmental data into your existing systems or applications using webhooks. For example, integrate air quality data into a weather app or smart home automation system to provide users with comprehensive environmental information.

Timely triggered campaigns

Integrate Ambee Webhooks with Google Sheets for campaign management. Marketers can trigger their campaigns based on specific environmental conditions. For instance, when certain thresholds are met, webhooks can instantly update the Google Sheet, prompting the launch of a tailored marketing campaign. This ensures campaigns are timely, data-driven, and highly responsive to evolving trends and customer interactions.

Automated data collection

Set up webhooks to automatically collect environmental data at specified intervals. Use this data for research, monitoring, or compliance purposes without manual intervention.

Real-time insights

Receive real-time environmental data updates through webhooks to provide users with up-to-the-minute information. For instance, alert users about sudden changes in air quality or weather conditions that may affect their plans or health.

Collaborative data sharing

Share environmental data with collaborators, partners, or customers using webhooks. Collaborate on research projects, environmental monitoring, or data-driven decision-making by providing access to real-time data.

Unlock the power of data analysis with Ambee Webhooks and Google sheets integration

Ambee Webhooks and Google Sheets integration empower users with efficient data analysis. It seamlessly collects real-time environmental data, like air quality and weather conditions, and transfers it to organized spreadsheets via webhooks. This integration facilitates easy visualization, charting, and analysis, enabling valuable insights into trends and correlations.

Whether for personal monitoring or business optimization, this solution streamlines data-driven decision-making. Embrace the power of data analysis through Ambee Webhooks and Google Sheets integration today and make informed choices that impact health or operations positively. Start integrating now for a more informed perspective on your environment or to enhance efficiency.