Example: Create a simple notification to alert Commercial team to check lead transfer.

**Part 1: Prepare the data sheet with data source from App Script**

I want the data as below, i prepare a sheet with header same as this:

Firstly, I prepare the script to get exactly from Bigquery:

SELECT  date_sent AS date ,  name, email, concat("\'",phone_number) as phone_number, address , city, state, postcode FROM `hhg-client.hhg_my_rb_enfa_2022.hhg_my_rb_enfa_2022_sent_di` where date_sent = current_date()

Then, open App Script by clicking "Extensions > App Script"
Then add this script:

Fill:
. projectId = [??]
. request = [??] <-- The Bigquery Script you prepared
. spreadsheet.getSheetByName('[??]') // Sheet you place data <-- The sheet name that you want to place result
. sheet.getRange(2, 1, sheet.getLastRow(), headers.length).clear(); <-- Delete this row if you don't want to clear the whole sheet before insert new data; in another word, remove this function will insert new data below the previous data
. ss.getSheetByName('[??]'); //Sheet you place the bot <-- The sheet name that you place the bot
. "text": sheet.getRange("B3").getValue() + "\n" + sheet.getRange('B4').getValue()
}; <-- B3 and B4 actually row 3 and 4, column B in sheet Bot, "\n" will space one row. This will help to send exact message we set from the begining

function example_script() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  var projectId = 'hhg-client';

  var request = { 
    query: 'SELECT  date_sent AS date ,  name, email, concat("\'",phone_number) as phone_number, address , city, state, postcode FROM `hhg-client.hhg_my_rb_enfa_2022.hhg_my_rb_enfa_2022_sent_di` where date_sent = current_date()',
    useLegacySql : false
  };
  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }
  // Get all the rows of results.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }
  
  // results spreadsheet.
  if (rows) {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getSheetByName('Data'); // Sheet you place data

    //headers.
    var headers = queryResults.schema.fields.map(function(field) {
      return field.name;
    });
    sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).clear();
    // Append the results.
    var data = new Array(rows.length);
    for (var i = 0; i < rows.length; i++) {
      var cols = rows[i].f;
      data[i] = new Array(cols.length);
      for (var j = 0; j < cols.length; j++) {
        data[i][j] = cols[j].v;
      }
    }
    // clear
    sheet.getRange(2, 1, sheet.getLastRow(), headers.length).clear();
    sheet.getRange(2, 1, rows.length, headers.length).setValues(data);

    Logger.log('Results spreadsheet created: %s',    spreadsheet.getUrl());
    var ss = SpreadsheetApp.getActiveSpreadsheet(); //get active spreadsheet (bound to this script)
    var sheet = ss.getSheetByName('Bot'); //Sheet you place the bot
    var sheetContents = sheet.getDataRange()
    var sheetContentsValues =  sheetContents.getValues();
    
    var sheetContents = sheetContentsValues.shift();
    // Fetch values for each row in the Range.
    
    Logger.log(sheetContentsValues);
    
    var message = {   
        "text":  sheet.getRange("B3").getValue() + "\n" + sheet.getRange('B4').getValue()
      };
    sendAlert(message);
  } else {
    Logger.log('No rows returned.');
  }
}

**PART 2: Message Template**

I want the message
"Dear stakeholders, please check RB Enfa 2023 lead today, we have 608 new leads.
Click here: a link"

Please note that 608 is from a formula linking with the main Data sheet,

Then I create a sheet like this

Let's name this sheet is "Bot".

**PART 3: Create Hangout wedhook ID**

You go to the Hangout Group > App & Intergrations
Add Webhooks
Fill Bot Name and Link of image
Click save and copy webhook ID

**PART 4: Notification Bot**
Go back to your app script and patse this script

Fill
. chatWebhook = [??] <-- webhook ID

function sendAlert (message) {
  const chatWebhook = 'https://chat.googleapis.com/v1/spaces/AAAAZVkhh38/messages?key=AIzaSyDdI0hCZtE6vySjMm-WEfRq3CPzqKqqsHI&token=hpu0emp2n01CtWSafljagEaqjSiSzi2CbCTGgxvcPNc';

  const options = {
    "method": "post", 
    "contentType": "application/json", 
   // "muteHttpExceptions": true, 
    "payload": JSON.stringify(message)
  };
  try {
  var result = UrlFetchApp.fetch(chatWebhook, options);
  } catch(result){
  Logger.log(result);
  }
}

Done, now your bot is ready, you can press here to test

Part 5: Schedule
Click on the Trigger > Add Trigger > add the right script name > schedule as you want

**Tips for success:**
1. Naming app script wisely (usually same name with google sheet), because when App script failed, Google will send you an alert. Naming correctly will help to identify which script failed and fix quickly.
2. Bigquery Script <-- Test the cost wisely, otherwise very hard to control Bigquery cost.