Refreshing connected Look in google scheet via appscript

[Shusan Khatiwada]

Hello,

I am trying to refresh a Look in Google Sheets using Apps Script. I’ve explored the time-based trigger option, but I would like to trigger the refresh based on an external event instead of a scheduled time.

Can anyone confirm if this is possible or suggest an approach?

For context, here’s the flow I’m trying to achieve:

• Pull a Look from Looker into Google Sheets
• Deploy the appscript as a web app and refresh the data based on a call from an external system.

Any guidance would be greatly appreciated!

[Kildere S Irineu]

Of course! This is an excellent and very practical use case for Google Apps Script.

To answer your question directly: Yes, this is absolutely possible. Your proposed approach of deploying the script as a web app is the correct way to achieve this.

The standard time-based trigger is for scheduled tasks, but for event-driven tasks initiated by an external system, a Web App acts as a webhook or a simple API endpoint.

Here is a complete, step-by-step guide and the code to implement your desired flow.

The Core Concept

The Looker Connected Sheets add-on exposes a specific Apps Script function: looker.run(). Our goal is to create a secure web endpoint that, when called, simply executes this function.

---

Step 1: Create the Apps Script Code

In your Google Sheet (the one with the Look you want to refresh), go to Extensions > Apps Script. Erase any existing code in the Code.gs file and replace it with the following:

    /**
 * @OnlyCurrentDoc
 * This script creates a secure web app to refresh a Looker Look on demand.
 */

// =================================================================
// MAIN WEB APP FUNCTION
// This function runs when an external system sends an HTTP POST request.
// =================================================================
function doPost(e) {
  const SECRET_TOKEN = PropertiesService.getScriptProperties().getProperty('SECRET_TOKEN');
  
  // --- Security Check ---
  // We first validate a secret token passed from the external system.
  try {
    const requestData = JSON.parse(e.postData.contents);
    if (!requestData.secret || requestData.secret !== SECRET_TOKEN) {
      return createJsonResponse({ status: 'error', message: 'Invalid or missing secret token.' }, 401); // 401 Unauthorized
    }
  } catch (err) {
    return createJsonResponse({ status: 'error', message: 'Invalid request format. Expected JSON.' }, 400); // 400 Bad Request
  }

  // --- Looker Refresh Logic ---
  try {
    // This is the core function provided by the Looker add-on to refresh data.
    looker.run(); 
    
    // If the refresh is successful, send a success response.
    return createJsonResponse({ status: 'success', message: 'Look refreshed successfully.' });
    
  } catch (error) {
    // If the refresh fails, capture the error and send a failure response.
    return createJsonResponse({ status: 'error', message: 'Failed to refresh Look.', details: error.toString() }, 500); // 500 Internal Server Error
  }
}

/**
 * Helper function to create a standardized JSON response for the web app.
 * @param {Object} data The payload to send back.
 * @param {number} [statusCode=200] The HTTP status code.
 * @return {GoogleAppsScript.Content.TextOutput} The JSON response object.
 */
function createJsonResponse(data, statusCode = 200) {
  const response = ContentService.createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
  
  // Note: Google Apps Script does not fully support setting HTTP status codes,
  // but including it in the response body is good practice for the calling system.
  data.statusCode = statusCode;
  
  return response;
}


// =================================================================
// SETUP FUNCTION (Run this only ONCE from the editor)
// This function securely stores your secret token.
// =================================================================
function setSecretToken() {
  // IMPORTANT: Replace "YOUR_SUPER_SECRET_TOKEN" with a long, random string.
  // You can generate one from a site like random.org or a password generator.
  const mySecret = "YOUR_SUPER_SECRET_TOKEN"; 
  
  PropertiesService.getScriptProperties().setProperty('SECRET_TOKEN', mySecret);
  
  Logger.log('Secret token has been set successfully.');
}
  

Step 2: Set Your Secret Token (One-time Setup)

For security, you don't want just anyone on the internet to be able to trigger your refresh. We use a secret token to ensure only authorized systems can call your endpoint.

1. In the code above, replace "YOUR_SUPER_SECRET_TOKEN" inside the setSecretToken function with your own unique, hard-to-guess string.

2. In the Apps Script editor, select the setSecretToken function from the dropdown menu at the top.

3. Click Run.

4. You will be asked to authorize the script. Follow the prompts. This will securely save your secret in the script's properties.

Step 3: Deploy the Script as a Web App

This is the crucial step that creates the public URL.

1. In the Apps Script editor, click the blue Deploy button in the top-right corner.

2. Select New deployment.

3. Click the gear icon next to "Select type" and choose Web app.

4. Fill in the deployment configuration:

   • Description: Looker Refresh Webhook (or something similar).

   • Execute as: Me (This is very important. It ensures the script runs with your permissions).

   • Who has access: Anyone (This is required for an external system to call the URL. The secret token we set up will provide the security).

5. Click Deploy.

6. A window will appear with the Web app URL. Copy this URL. This is the endpoint your external system will call.

Step 4: Call the Web App from Your External System

Now, your external system just needs to send an HTTP POST request to the URL you copied.

The request must include:

• Method: POST

• Header: Content-Type: application/json

• Body (raw JSON):

  s

  IGNORE_WHEN_COPYING_START

  IGNORE_WHEN_COPYING_END

      {
    "secret": "YOUR_SUPER_SECRET_TOKEN"
  }
    

  (Use the same secret you defined in Step 2).

Example using cURL:

IGNORE_WHEN_COPYING_START

IGNORE_WHEN_COPYING_END

    curl -L -X POST 'YOUR_DEPLOYED_WEB_APP_URL' \
-H 'Content-Type: application/json' \
--data-raw '{
    "secret": "YOUR_SUPER_SECRET_TOKEN"
}'
  

If successful, the response from the webhook will be:
{"status":"success","message":"Look refreshed successfully.","statusCode":200}

If the secret is wrong, the response will be:
{"status":"error","message":"Invalid or missing secret token.","statusCode":401}

You have now successfully created an event-driven way to refresh your Looker data in Google Sheets

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/c4db19d5-c603-4241-9e42-fd338abd3067n%40googlegroups.com.