Can onEdit create a sidebar ?

ID03 AllDataBiz

unread,

Aug 10, 2023, 8:34:44 AM8/10/23

to Google Apps Script Community

I am trying to write code for sidebar can be created in onEdit, but sidebar did not appear.
can anybody hep me please?

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  
  // Check if the edited cell is in the active sheet
  if (sheet.getName() == e.range.getSheet().getName()) {

    Browser.msgBox('A cell in this sheet has been changed !');

    openSidebar();

  }
}

function openSidebar() {
  var sidebarHtml = HtmlService.createHtmlOutputFromFile('Sidebar')
    .setTitle('Custom Sidebar')
    .setWidth(300);
  
  SpreadsheetApp.getUi().showSidebar(sidebarHtml);
}
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    
  </body>
</html>

Mr Shane

unread,

Aug 31, 2023, 9:45:09 PM8/31/23

to Google Apps Script Community

You need to set this up with an installable trigger, then it will work.

Message has been deleted

Lapiaute

unread,

Sep 1, 2023, 3:26:04 PM9/1/23

to Google Apps Script Community

The code you provided seems mostly correct for creating a custom sidebar in Google Sheets using Google Apps Script and HTMLService. However, there are a few things to check and modify to ensure it works as expected:

Enable Google Apps Script API: Make sure you have enabled the Google Apps Script API for your project in the Google Cloud Console.

Correct HTML File: Ensure that you have an HTML file named 'Sidebar.html' in your Google Apps Script project. This file should contain the content you want to display in the sidebar.

Installable Trigger: The onEdit trigger you are using won't work if you run it from the script editor because Browser.msgBox and showSidebar require an installable trigger. To set up an installable trigger, follow these steps:

Click on the clock icon (Triggers) in the Apps Script editor.
Click on the "+ Add Trigger" button.
Set up the trigger to run the onEdit function with the event source set to the spreadsheet.
Save the trigger.
Authorization: The user running the script must authorize the script to access their Google Sheets. Ensure that you've gone through the authorization process.

Test with a Manual Edit: To test your code, you'll need to manually edit a cell in the spreadsheet that triggers the onEdit function.

Publish Your Script: Make sure your script is properly published as a web app with the correct permissions. Follow these steps:

In the Google Apps Script editor, go to File > Project properties > Script properties.
Set the showSidebar function to a number, e.g., 1.
Go to Publish > Deploy as web app.
Choose "Me" or "Anyone within [your organization]" for "Who has access to the app."
Click "Deploy."
Copy the "Current web app URL" and use it to authorize the script for the first time.
Popup Blockers: Sometimes, browser pop-up blockers can prevent the sidebar from opening. Make sure your browser settings allow pop-ups from Google Sheets.

After checking and modifying the above points, your code should work, and the custom sidebar should appear when a cell is edited in the spreadsheet.

Reply all

Reply to author

Forward