Actualizar periódicamente la función de hoja de cálculo IMPORTXML ()

Solución:

Hice un par de ajustes a la respuesta de Mogsdad:

• Arreglado el releaseLock() colocación de llamada
• Actualiza (o agrega) un parámetro de cadena de consulta a la URL en la función de importación (en lugar de almacenar, eliminar, esperar 5 segundos y luego restaurar todas las fórmulas relevantes)
• Funciona en una hoja específica en su hoja de cálculo
• Muestra la hora de la última actualización

…

function RefreshImports() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return;             // Wait up to 5s for previous refresh to end.

  var id = "[YOUR SPREADSHEET ID]";
  var ss = SpreadsheetApp.openById(id);
  var sheet = ss.getSheetByName("[SHEET NAME]");
  var dataRange = sheet.getDataRange();
  var formulas = dataRange.getFormulas();
  var content = "";
  var now = new Date();
  var time = now.getTime();
  var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range)(.*/gi;
  var re2 = /((?|&)(update=[0-9]*))/gi;
  var re3 = /(",)/gi;

  for (var row=0; row<formulas.length; row++) {
    for (var col=0; col<formulas[0].length; col++) {
      content = formulas[row][col];
      if (content != "") {
        var match = content.search(re);
        if (match !== -1 ) {
          // import function is used in this cell
          var updatedContent = content.toString().replace(re2,"$2update=" + time);
          if (updatedContent == content) {
            // No querystring exists yet in url
            updatedContent = content.toString().replace(re3,"?update=" + time + "$1");
          }
          // Update url in formula with querystring param
          sheet.getRange(row+1, col+1).setFormula(updatedContent);
        }
      }
    }
  }

  // Done refresh; release the lock.
  lock.releaseLock();

  // Show last updated time on sheet somewhere
  sheet.getRange(7,2).setValue("Rates were last updated at " + now.toLocaleTimeString())
}

La pregunta de actualización de la fila de la hoja de cálculo de Google y sus respuestas se refieren a las “Hojas de cálculo antiguas”, que tenían un comportamiento diferente al de la versión 2015 de Hojas de cálculo de Google. No hay actualización automática de contenido con “Hojas nuevas”; los cambios solo se evalúan ahora en respuesta a las ediciones.

Si bien Sheets ya no ofrece esta capacidad de forma nativa, podemos usar un script para actualizar las fórmulas de “importación” (IMPORTXML, IMPORTDATA, IMPORTHTML y IMPORTANGE).

Script de utilidad

Para la actualización periódica de IMPORTAR fórmulas, configure esta función como un disparador impulsado por el tiempo.

Advertencias:

• Función de importación Cambios de fórmula realizados en la hoja de cálculo por otros scripts o usuarios durante el período de actualización PODRÍA SER SOBRESCRITO.
• La superposición de actualizaciones puede hacer que su hoja de cálculo sea inestable. Para mitigar eso, el script de utilidad usa un ScriptLock. Esto puede entrar en conflicto con otros usos de ese bloqueo en su secuencia de comandos.

/**
 * Go through all sheets in a spreadsheet, identify and remove all spreadsheet
 * import functions, then replace them a while later. This causes a "refresh"
 * of the "import" functions. For periodic refresh of these formulas, set this
 * function up as a time-based trigger.
 *
 * Caution: Formula changes made to the spreadsheet by other scripts or users
 * during the refresh period COULD BE OVERWRITTEN.
 *
 * From: https://stackoverflow.com/a/33875957/1677912
 */
function RefreshImports() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return;             // Wait up to 5s for previous refresh to end.
  // At this point, we are holding the lock.

  var id = "YOUR-SHEET-ID";
  var ss = SpreadsheetApp.openById(id);
  var sheets = ss.getSheets();

  for (var sheetNum=0; sheetNum<sheets.length; sheetNum++) {
    var sheet = sheets[sheetNum];
    var dataRange = sheet.getDataRange();
    var formulas = dataRange.getFormulas();
    var tempFormulas = [];
    for (var row=0; row<formulas.length; row++) {
      for (col=0; col<formulas[0].length; col++) {
        // Blank all formulas containing any "import" function
        // See https://regex101.com/r/bE7fJ6/2
        var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range)(.*/gi;
        if (formulas[row][col].search(re) !== -1 ) {
          tempFormulas.push({row:row+1,
                             col:col+1,
                             formula:formulas[row][col]});
          sheet.getRange(row+1, col+1).setFormula("");
        }
      }
    }

    // After a pause, replace the import functions
    Utilities.sleep(5000);
    for (var i=0; i<tempFormulas.length; i++) {
      var cell = tempFormulas[i];
      sheet.getRange( cell.row, cell.col ).setFormula(cell.formula)
    }

    // Done refresh; release the lock.
    lock.releaseLock();
  }
}

Para responder a su pregunta de un “disparador” fácil para forzar la recarga de la función:

agregue un parámetro adicional no utilizado a la URL que está cargando, mientras hace referencia a una celda para el valor de ese parámetro. Una vez que modifica el contenido de esa celda, la función se vuelve a cargar.

ejemplo:

importxml("http://www.example.com/?noop=" & $A$1,"...")

desafortunadamente, no puede colocar una función de cálculo de fecha en la celda referenciada, lo que arroja un error de que esto no está permitido.

---

---

Utiliza Nuestro Buscador