Add Refresh Button in 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.
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
Apps Script Editor
- 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
Apps Script Settings Tab
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.
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.
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());
}
}
Get All Sheet IDs output in Apps Script
Now paste the remaining code in the Apps Script editor:
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.
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.
Was this page helpful?