打开我的活动Google表格时,我的onSheetOpen / showSidebar不适用于任何人。我已经设置了触发器来为我自己启动onSheetOpen和showSidebar,但这对打开电子表格的其他人无效。谁能帮忙弄清楚为什么会这样吗?下面是我正在使用的代码。

 /**
 * Set up custom sidebar.
 * TRIGGER: spreadsheet event 'on open'
 */
function onSheetOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Actions')
    .addItem("Register interest in current opportunity", 'registerInterest')
    .addItem("Show sidebar", 'showSidebar')
    .addToUi();

  showSidebar();
}

/**
 * Renders the sidebar
 * TRIGGER: spreadsheet event 'on open' & custom menu option
 */
function showSidebar() {
  try {
    var htmlOutput = HtmlService.createHtmlOutputFromFile('sidebar');
    SpreadsheetApp.getUi().showSidebar(htmlOutput);
  } catch(e) {
    // Just ignore - means user doesn't have edit access to sheet
  }
}



/**
 * Main function. Grabs the required data from the currently-selected row, registers interest
 * in that data in a separate sheet, and sends a confirmation email to the user.
 */
function registerInterest() {
  //var spreadsheet = SpreadsheetApp.getActive();
  // These are the attributes we capture and store in the 'interest' sheet
  var who = Session.getActiveUser().getEmail();
  var when = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss');
  var data = getRowOfInterest(SpreadsheetApp.getActiveSheet());
  var id = Utilities.getUuid();
  Logger.log(id)
  if(data.what && data.what.length > 0) {
    // Get the Google Sheets ID of the target 'interest' sheet
    var id = getConfigValue("InterestSheet");
    if(id) {
      // Now try and open that sheet for updating
      var ss = SpreadsheetApp.openById(id);
      if(ss) {
        var sheet = ss.getSheetByName('Interest');
        if(sheet) {
          // All good; log interest. First, create the new row of data for the target sheet
          sheet.appendRow([ when, who, data.what, data.subject,  data.location, data.industry, data.capabilities ]);
          // Second, grab our 'template' email from config & use that to send a confirmation
          // email to the person registering their interest
          var body = getConfigValue('InterestEmail');
          MailApp.sendEmail(who, getConfigValue('InterestSubject'), null, { noReply: true, htmlBody: body });
        } else {
          throw Error('can\'t open the \'Interest expressed\' sheet (or it doesn\'t exist)');
        }
      } else {
        throw Error('can\'t open the \'Interest expressed\' sheet');
      }
    } else {
      throw Error('\'Interest expressed\' sheet not specified');
    }
  }
}

/**
 * Utility to derive the required data for registering interest.
 * Called from the registerInterest() function
 */
function getRowOfInterest(sheet) {
  var result = {};
  var row = sheet.getActiveRange().getRow(); // Get the currently-selected row index
  var fullRange = sheet.getRange(row, 1, 1, sheet.getLastColumn()); // Get the entire row of data
  //var fullRange = sheet.getRange(row, 1, 1, 9);
  Logger.log(fullRange.getValues())
  var data = fullRange.getValues();
  if(data[0] && data[0].length > 0) {
    for(var n = 0; n < data[0].length; n++) {
      // Populate specific attributes in the 'result' object - tailor this as you see fit
      if(n==0) result.subject = data[0][n];
      if(n==6) result.what = data[0][n] + ' ';
      //if(n==5 || n==7) result.what += data[0][n] + ' ';
      if(n==7) result.location = data[0][n];
      if(n==11) result.industry = data[0][n];
      if(n==12) result.capabilities = data[0][n];
    }

    result.what += '(' + sheet.getName() + ')';
    result.industry;
    result.capabilities;
    return result;
  }
}

function getSomeRange(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var row = sheet.getActiveRange().getRow(); // Get the currently-selected row index

  var myRange = sheet.getRange(row, 1, 1, sheet.getLastColumn());

  //Logger.log("Number of rows in range is "+myRange.getNumRows()+ " Number of columns in range is "+ myRange.getNumColumns());

}
/**
 * Utility to pull specified data from a config sheet. This assumes that a sheet called 'Config'
 * is populated thus:
 *    Column contains a load of 'key' and column 2 contains the corresponding 'values'
 * Called from registerInterest() function
 */
function getConfigValue(key) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Config');
  var result;

  if(sheet) {
    // Scan column 1 for key and return value in column 2
    var data = sheet.getDataRange().getValues();
    for(var n = 0; n < data.length; ++n) {
      if(data[n][0].toString().match(key)) result = data[n][1];
    }
  }

  return result;
}

function hideExpiredEvents() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Events');
  var exsh=ss.getSheetByName('Expired Events');
  var sr=2;
  var rg=sh.getRange(sr,1,sh.getLastRow()-sr+1,sh.getLastColumn());
  var vA=rg.getValues();
  Logger.log(vA);
  var dt=new Date();
  var  d=0;//deleted row counter
  var today=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate(0)).valueOf();
  for(var i=0;i<vA.length;i++) {
    if(new Date(vA[i][3]).valueOf()<today) {
      exsh.appendRow(vA[i]);//move to bottom of expired events
      sh.deleteRow(i+sr-d++);//increment deleted row counter
    }
  }
}

最佳答案

不幸的是,看来您遇到的问题可能是一个错误。

在这种情况下,您可以做的是通过单击问题编号旁边的★在问题跟踪器here上为问题加注星标,并发表评论,并说您受此问题影响。

08-08 03:56