在Google Apps脚本中触发importHTML [英] Trigger importHTML in Google Apps Script

问题描述

我想知道是否可以编写一个Google Apps脚本来触发Google电子表格中的每日importhtml.例如，假设我要每天下午1点导入下表的更新.

I'd like to know whether it is possible to write a Google Apps Script that will trigger daily importhtml in Google spreadsheets. For example let's assume I want to import updates of following table everyday at 1 pm.

=IMPORTHTML("https://en.wikipedia.org/wiki/Lubbock,_Texas","table",5)

即使我的计算机离线，我也需要这种自动检查表更新的方法. 谢谢

I need this automatic checking for table updates even when my computer is offline. Thank you

推荐答案

是的，每天都可以编写脚本来制作表的副本. 简而言之，您将必须复制导入的表格并将其粘贴到新的工作表中.每次您打开/访问电子表格时，ImportHTML a都会更新.因此，存储表的唯一方法是进行复制并将其粘贴到新的工作表中.

Yes, it is possible to write a script to make a copy of the table every day. In short, you will have to make a copy of the imported table and paste it into a new Sheet. ImportHTML a gets updated every time you open/access the spreadsheet. So only way to store table is to make a copy and paste it into a new sheet.

这是执行此操作的代码:

Here is code that does just that:

function getImportData()
{
 var ss = SpreadsheetApp.getActive()
var sheet =ss.getSheetByName("ImportSheet")

if (sheet == null)
{  
sheet = ss.insertSheet("ImportSheet")
sheet.getRange(1,1).setValue( "=IMPORTHTML(\"https://en.wikipedia.org/wiki/Lubbock,_Texas\",\"table\",5)")
}
var dataRange = sheet.getDataRange()
//waitforLoading waits for the function importHTML to finish loading the table, only a problem if table takes a while to load
//for more discussion on this visit : http://stackoverflow.com/questions/12711072/how-to-pause-app-scripts-until-spreadsheet-finishes-calculation
var wait = waitForLoading(dataRange,sheet, 10)
var logSheet = ss.getSheetByName("ImportLogSheet")
 if (logSheet == null)
  {  
    logSheet = ss.insertSheet("ImportLogSheet")
  }
  var timeStampRow = []
  timeStampRow[0] = new Date()
if(wait)
{
  logSheet.appendRow(timeStampRow)
  var lastRow = logSheet.getLastRow() + 1
  var destinationRange = logSheet.getRange(lastRow,1)
  dataRange.copyTo(destinationRange, {contentsOnly:true})  

}
  else {
    timeStampRow[1] = "Scripted timeout waiting for the table to Load " 
    logSheet.appendRow(timeStampRow)
  }
}

function waitForLoading(dataRange, sheet, maxWaitTimeInSec)
{
 // This function is only required if it takes a while for the importHTML to load your data!
 // if not you can skip this function

  // Function looks to see if the value of the  
for(i = 0; i< maxWaitTimeInSec ; i++)
{
  var value = dataRange.getCell(1,1).getValue()
  if(value.search("Loading") !== -1) {
    Utilities.sleep(1000);
    dataRange = sheet.getDataRange()
  } else {
   return true
  }

}
  return false

}

编辑:忘记提及设置触发器.您可以设置任何要使用时间驱动触发器来触发的功能.有关设置的详细信息，请参见: https://developers .google.com/apps-script/guides/triggers/installable#managing_triggers_manually

Forgot to mention about setting up triggers. You can setup any function to be triggered using time-driven triggers. Details on how to set it up is given here: https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually

这篇关于在Google Apps脚本中触发importHTML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！