问在google sheet中运行脚本后，有没有办法自动显示单元格数据验证下拉列表中的下一个(下一个项目)？
EN

Stack Overflow用户

提问于 2021-09-09 20:21:17

回答 1查看 47关注 0票数 2

我目前是谷歌工作表脚本的新手。我正在使用脚本在google sheet中做一个简单的报告生成器。我已经有一个脚本，可以将生成的pdf文件保存到google驱动器。在我的google工作表中，有一个引用到主数据源的单元格，它带有下拉数据验证，选择人员的电子邮件，并将生成其他详细信息(它也是基于同一工作表中的该电子邮件的引用)。我想要的是，当我运行脚本来保存pdf文件，它应该自动生成pdf文件的下拉验证列表中的每个项目。这个是可能的吗？任何知道这一点的人请。

这是我的代码，生成pdf文件到驱动器(我也是从网上得到的，并做了一些修改)…非常感谢你提前这么做。

var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet2 = ss.getSheetByName('ID');
  var gdid1 = sheet2.getRange('B1').getValue();
  var gdid2 = sheet2.getRange('B4').getValue();

function onOpen() {
  var ui = SpreadsheetApp.getUi()
    ui.createMenu('ExportGDrive')
    .addItem('Export BasicED', 'exportBasicED')
    .addItem('Export College', 'exportCollege')
    .addToUi()
}


function _exportBasic(blob, fileName, spreadsheet) {
  blob = blob.setName(fileName)
  var folder = DriveApp.getFolderById(gdid1)
  var pdfFile = folder.createFile(blob)
  
  // Display a modal dialog box with custom HtmlService content.
  const htmlOutput = HtmlService
    .createHtmlOutput('<p>Click to open <a href="' + pdfFile.getUrl() + '" target="_blank">' + fileName + '</a></p>')
    .setWidth(300)
    .setHeight(80)
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
}

function _exportCollege(blob, fileName, spreadsheet) {
  blob = blob.setName(fileName)
  var folder = DriveApp.getFolderById(gdid2)
  var pdfFile = folder.createFile(blob)
  
  // Display a modal dialog box with custom HtmlService content.
  const htmlOutput = HtmlService
    .createHtmlOutput('<p>Click to open <a href="' + pdfFile.getUrl() + '" target="_blank">' + fileName + '</a></p>')
    .setWidth(300)
    .setHeight(80)
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
}



function exportAsPDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var blob = _getAsBlob(spreadsheet.getUrl())
  _exportBlob(blob, spreadsheet.getName(), spreadsheet)
}

function _getAsBlob(url, sheet, range) {
  var rangeParam = ''
  var sheetParam = ''
  if (range) {
    rangeParam =
      '&r1=' + (range.getRow() - 1)
      + '&r2=' + range.getLastRow()
      + '&c1=' + (range.getColumn() - 1)
      + '&c2=' + range.getLastColumn()
  }
  if (sheet) {
    sheetParam = '&gid=' + sheet.getSheetId()
  }
  // A credit to https://gist.github.com/Spencer-Easton/78f9867a691e549c9c70
  // these parameters are reverse-engineered (not officially documented by Google)
  // they may break overtime.
  var exportUrl = url.replace(/\/edit.*$/, '')
      + '/export?exportFormat=pdf&format=pdf'
      + '&size=LETTER'
      + '&portrait=true'
      + '&fitw=true'       
      + '&top_margin=0.75'              
      + '&bottom_margin=0.75'          
      + '&left_margin=0.7'             
      + '&right_margin=0.7'           
      + '&sheetnames=false&printtitle=false'
      + '&pagenum=UNDEFINED' // change it to CENTER to print page numbers
      + '&gridlines=FALSE'
      + '&fzr=FALSE'      
      + sheetParam
      + rangeParam
      
  Logger.log('exportUrl=' + exportUrl)
  var response
  var i = 0
  for (; i < 5; i += 1) {
    response = UrlFetchApp.fetch(exportUrl, {
      muteHttpExceptions: true,
      headers: { 
        Authorization: 'Bearer ' +  ScriptApp.getOAuthToken(),
      },
    })
    if (response.getResponseCode() === 429) {
      // printing too fast, retrying
      Utilities.sleep(3000)
    } else {
      break
    }
  }
  
  if (i === 5) {
    throw new Error('Printing failed. Too many sheets to print.')
  }
  
  return response.getBlob()
}


function exportCurrentSheetAsPDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var currentSheet = SpreadsheetApp.getActiveSheet()
  
  // Change the Cell for the name of the file (example: B3 for current sheet)
  var blob = _getAsBlob(spreadsheet.getUrl(), currentSheet)
  _exportBlob(blob, SpreadsheetApp.getActiveSheet().getRange('B3').getValue(), spreadsheet)
}



function exportBasicED() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var sheet1 = spreadsheet.getSheetByName('Generate-Basic Ed');
  var name = sheet1.getRange('B3').getValue();
  // Change the Cell for the name of the file (example: B3 for current sheet)
  var blob = _getAsBlob(spreadsheet.getUrl(), sheet1)
  _exportBasic(blob, name, spreadsheet)
}

function exportCollege() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var sheet1 = spreadsheet.getSheetByName('Generate-College');
  var name = sheet1.getRange('B3').getValue();
  // Change the Cell for the name of the file (example: B3 for current sheet)
  var blob = _getAsBlob(spreadsheet.getUrl(), sheet1)
  _exportCollege(blob, name, spreadsheet)
}

google-apps-script

google-sheets

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-09-09 22:42:10

试一试

function nextItem() {
  var sh = SpreadsheetApp.getActive();
  var data = sh.getRange('myValidationList').getValues();
  for (var i = 0; i < data.length; i++) {
    sh.getRange('A2').setValue(data[i][0]);
    SpreadsheetApp.flush(); 
    // here you can call the script to send pdf
    Utilities.sleep(1000);
  }
};