Appscript

[prashantshu]

How to make the appscript that can take the Google trends with multiple queries with 5 batches and post the data in sheet

[Community Alums]

hi @prashantshu   ,

1. Set Up Your Google Sheet

Create a new Google Sheet and name the first sheet something like TrendsData. This is where the data will go.

2. Open Apps Script

In the sheet, go to Extensions > Apps Script and paste the following starter code:

function getGoogleTrendsData() {
  const queries = [
    "ServiceNow", "ITSM", "Incident Management", "Change Management", "CMDB",
    "HR Service Delivery", "Customer Service Management", "Flow Designer", "App Engine", "Virtual Agent"
  ];

  const batchSize = 5;
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TrendsData");
  sheet.clear(); // Clear old data
  sheet.appendRow(["Query", "Interest Over Time"]);

  for (let i = 0; i < queries.length; i += batchSize) {
    const batch = queries.slice(i, i + batchSize);

    batch.forEach(query => {
      // Simulate pulling data (replace with actual API call or workaround)
      const dummyData = Math.floor(Math.random() * 100); // Replace with real data
      sheet.appendRow([query, dummyData]);
    });

    Utilities.sleep(2000); // Pause between batches to avoid rate limits
  }
}

3. Schedule the Script

Go to Triggers in Apps Script and set it to run daily or hourly — whatever fits your needs.

---

4. Push Data to ServiceNow (Optional)

If you want to send this data to ServiceNow:

• Create a Scripted REST API in ServiceNow.
• Use UrlFetchApp in Apps Script to POST the data:

function sendToServiceNow(query, value) {
  const url = "https://yourinstance.service-now.com/api/your_endpoint";
  const payload = {
    query: query,
    value: value
  };

  const options = {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    headers: {
      "Authorization": "Basic " + Utilities.base64Encode("username:password")
    }
  };

  UrlFetchApp.fetch(url, options);
}