Add Refresh Button in Sheets

Only available on Google Sheets

Motivation

You can share the Google Sheet with your customers or team members, and they can refresh the data themselves by simply clicking the refresh button. They do not need to register for Superjoin or manage an API key on their own. All the logic and API authentication are handled within the sample Apps Script function, making the process seamless for anyone using the sheet. This feature allows you to:

Maintain control over the API key.
Ensure that customers can always access the latest data without needing to manually update it.
Avoid the need for additional Superjoin registrations for each customer.

The refresh functionality applies only to sheets where only the initial data pull was done using Superjoin’s Google Sheet Add on. Once data has been imported into a sheet via Superjoin, anyone the spreadsheet is shared with can refresh that data using the API key provided by the owner, ensuring easy and seamless updates.

Step by Step Guide

Get an API Key 🔑

To use this feature, you need to obtain an API key from Superjoin, by contacting us at support@superjoin.ai.

Store the API Key

To store your API key:

Open your target spreadsheet in Google Sheets
Navigate to Extensions > Apps Script
In the Apps Script editor, click the Settings icon in the left panel

Scroll down to the Script Properties section
Click “Add script property”
Set the property name as “API_KEY”
Set the value to the API key provided by Superjoin support

This securely stores your API key for use in the refresh function.

Create the Apps Script Function for Refreshing Data

In this step, we will create a function to refresh the data in the active Google Sheet.

The sheetId used in this function refers to the ID of the active sheet where the Apps Script editor is opened. This means the function will refresh data for the currently active sheet. If you’re working on a specific sheet in the editor, the refresh will be applied to that sheet alone.

Refresh Data for Active Sheet

function refreshData() {
  try {
    const apiKey = PropertiesService.getScriptProperties().getProperty('API_KEY');
    if (!apiKey) {
      Logger.log("Missing API Key");
      return;
    }
    const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
    const sheetId = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId();
    const BASE_URL = "https://api.superjoin.ai/public/v1";
    const options = {
      method: "post",
      headers: {
        authorization: `Api-Key ${apiKey}`,
      },
      contentType: 'application/json',
      payload: JSON.stringify({
        spreadsheetId,
        sheetId,
        metadata: {
          userEmailAddress: Session.getActiveUser().getEmail(),
          identifier: "EXTRA_IDENTIFIER"
        }
      })
    };
    const response = UrlFetchApp.fetch(`${BASE_URL}/import/refresh-data`, options);
    if (response.getResponseCode() === 200) {
      Logger.log("Data refresh initiated");
    }
  } catch (error) {
    Logger.log(`Error while refreshing data: ${error.message}`);
  }
}

In case you want to refresh multiple sheets, with a single click, you can use the follow the below instructions:Execute the below function to get all the sheet IDs along with their names, and then replace the sheetIds array with the sheet IDs you want to refresh in the refreshDataForMultipleSheets function.

Get All Sheet IDs

function getAllSheetIds() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    Logger.log(sheet.getSheetName() + ": " + sheet.getSheetId());
  }
}

Now paste the remaining code in the Apps Script editor:

Refresh Data for Multiple Sheets

function refreshDataForMultipleSheets() {
  const sheetIds = ["SHEET_ID_1", "SHEET_ID_2", ...]; // Replace with your sheet IDs

  const apiKey = PropertiesService.getScriptProperties().getProperty('API_KEY');
  if (!apiKey) {
    Logger.log("Missing API Key");
    return;
  }

  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  for (let sheetId of sheetIds) {
    refreshData(apiKey, spreadsheetId, sheetId);
  }
}

function refreshData(apiKey, spreadsheetId, sheetId) {
  try {
    const BASE_URL = "https://api.superjoin.ai/public/v1";
    const options = {
      method: "post",
      headers: {
        authorization: `Api-Key ${apiKey}`,
      },
      contentType: 'application/json',
      payload: JSON.stringify({
        spreadsheetId,
        sheetId,
        metadata: {
          userEmailAddress: Session.getActiveUser().getEmail(),
        }
      })
    };

    const response = UrlFetchApp.fetch(`${BASE_URL}/import/refresh-data`, options);
    if (response.getResponseCode() === 200) {
      Logger.log(`Data refresh initiated for Sheet ID: ${sheetId}`);
    }
  } catch (error) {
    Logger.log(`Error while refreshing data for Sheet ID ${sheetId}: ${error.message}`);
  }
}

Do not forget to save the Apps Script file after making the changes, using the Floppy icon.

Insert a Button in the spreadsheet

Go to the Insert menu of the same spreadsheet. Select Drawing from the dropdown options. In the drawing editor, create a rectangle and add text, such as “Refresh Data”. You can customize the appearance as per your preference.

Assign the refresh function to the Button

After inserting the button, click on the three dots in the top-right corner of the button and select Assign Script. In the dialog box that appears, enter the name of the function (e.g., refreshData). Click OK to assign the function to the button.

In case, you are refreshing multiple sheets, use the script name as refreshDataForMultipleSheets

Run the Script 🎉

Run the script throught the button you created in the first step, by clicking on it, and you will see the data is refreshed, in a few seconds or minutes (depends on the size of the data you have).

Need more help?

If you need more help, you can always contact us at support@superjoin.ai.

Getting Started

Features

Add Refresh Button in Sheets

Motivation

Step by Step Guide

Get an API Key 🔑

Store the API Key

Create the Apps Script Function for Refreshing Data

Insert a Button in the spreadsheet

Assign the refresh function to the Button

Run the Script 🎉

Need more help?

​Motivation

​Step by Step Guide

Get an API Key 🔑

Store the API Key

Create the Apps Script Function for Refreshing Data

Insert a Button in the spreadsheet

Assign the refresh function to the Button

Run the Script 🎉

Need more help?

Motivation

Step by Step Guide