Cómo eliminar una celda y volver a ingresar los mismos detalles en Google Sheets

Question

Actualmente estoy creando una forma de automatizar la obtención de datos del precio de las acciones.

Al principio, pensé que para actualizar el valor de la celda, solo necesito actualizar la declaración importxml en la celda. así que utilicé este código de la función de hoja de cálculo IMPORTXML () de actualización periódica

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();
  }
}

aparentemente, NO actualiza la celda ... así que descubrí que para actualizar la celda, necesito

1. eliminar el nombre de la acción
2. vuelva a introducir el nombre de la acción

aquí hay un video que muestra lo que quise decir con eso (solo para dejarlo en claro) https://streamable.com/eciks0

¿Cómo puedo automatizar esto, tal vez usando el script de hoja de Google?

gracias

---

EDITAR: aquí hay una copia de la hoja de Google con la que estoy trabajando

https://docs.google.com/spreadsheets/d/1BFz3LHWEw-wT9exJv558mAFOv-fIKPINaplmzRY24kw/edit?usp=sharing

por favor intente hacer una copia.

Gracias de nuevo por la ayuda

Answer 1

De su video de muestra, en su situación, pensé que cuando se incluyen los valores de las celdas "B9" y "B10" en urly xpathde la fórmula =IMPORTXML(url, xpath), cuando se cambia el valor de la celda "B1", la fórmula se actualiza. Entonces, ¿qué tal el siguiente script de muestra?

Ejemplo de guion:

Copie y pegue la siguiente secuencia de comandos en el editor de secuencias de comandos de la hoja de cálculo de Google y ejecute la función de myFunction. En esta secuencia de comandos de muestra, el valor de la celda "B1" se sobrescribe con newValue.

function myFunction() {
  var newValue = "###"; // Please set the new value.
  var sheetName = "Sheet1";  // Please set the sheet name.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.getRange("B1").setValue(newValue);
}

• Si la secuencia de comandos anterior no es útil y cuando también desea usar su secuencia de comandos en su pregunta, puede usar la siguiente secuencia de comandos. En este caso, configúrelo var id = "YOUR-SHEET-ID";para su situación real.

    function myFunction() {
      var newValue = "###"; // Please set the new value.
      var sheetName = "Sheet1";  // Please set the sheet name.
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
      sheet.getRange("B1").setValue(newValue);
      RefreshImports(); // <--- Added
    }
  

• O creo que podría usar el siguiente script para actualizar las fórmulas en la hoja.

    function myFunction2() {
      var newValue = "###"; // Please set the new value.
      var sheetName = "Sheet1";  // Please set the sheet name.
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
      sheet.getRange("B1").setValue(newValue);
  
      var formula = "=";
      var tempFormula = "=sample";
      sheet.createTextFinder(`^\\${formula}`).matchFormulaText(true).useRegularExpression(true).replaceAllWith(tempFormula); sheet.createTextFinder(`^\\${tempFormula}`).matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);
    }
  

Referencias:

• Temas relacionados
  • Funciones personalizadas y recálculo
  • Función AHORA () + Configuración de zona horaria (Hojas de cálculo de Google)

Adicional:

De su respuesta y su video compartido, ¿qué tal el siguiente guión de muestra? En este caso, como un simple script, la celda se borra y se acenvuelve a poner el valor de .

Ejemplo de guion:

function myFunction() {
  var newValue = "acen"; // Please set the new value.
  var sheetName = "Sheet1";  // Please set the sheet name.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getRange("B1");
  range.clearContent();
  SpreadsheetApp.flush();
  range.setValue(newValue);
}

Answer 2

GetEmCheckEm y BouncEm

La función superior le permite seleccionar todas las celdas que desea rebotar. Use la tecla de control y seleccione todas las celdas y luego ejecute getThem ();

function getThem() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  const rgl=sh.getActiveRangeList();
  let rlA=[];
  rgl.getRanges().forEach(function(rg,i){                      
    let h=rg.getHeight();
    let w=rg.getWidth();
    let row=rg.getRow();
    let col=rg.getColumn();
    for(let i=0;i<h;i++) {
      for(let j=0;j<w;j++) {
        rlA.push(sh.getRange(Number(row+i),Number(col+j)).getA1Notation());
      } 
     }
    });
  PropertiesService.getScriptProperties().setProperty('mystocks',JSON.stringify(rlA));
  ss.toast("Process Complete");  
}

Una vez que los tenga, puede hacer clic en la pantalla para borrar sus selecciones. La ejecución checkThem () y sus selecciones deberían volver a aparecer porque deberían haberse guardado en PropertiesService.

function checkThem() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rangeList=sh.getRangeList(JSON.parse(PropertiesService.getScriptProperties().getProperty('mystocks')));
  sh.setActiveRangeList(rangeList);
  ss.toast('Process Complete');
}

Ahora puede ejecutar bounceThem () y los valores desaparecerán y luego volverán a aparecer

function bounceThem() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const list=sh.getRangeList(JSON.parse(PropertiesService.getScriptProperties().getProperty('mystocks')));
  let data=[];
  list.getRanges().forEach(r=>{data.push(r.getValue());r.setValue('');});
  SpreadsheetApp.flush();
  Utilities.sleep(5000);
  list.getRanges().forEach((r,i)=>{r.setValue(data[i]);});
  SpreadsheetApp.flush();
  ss.toast("Process Complete");
}

Encontré esto bastante ejecutado, así que regresé esta mañana y lo configuré para usar setValues ​​() en lugar de setValue () y ahora es mucho más rápido hacer el rebote.

function getThem1() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  const rgl=sh.getActiveRangeList();
  console.log(sh.getName());
  let rlA=rgl.getRanges().map(function(rg){return rg.getA1Notation();});
  PropertiesService.getScriptProperties().setProperty('mystocks1',JSON.stringify(rlA));//Stored as JSON in property service
  ss.toast("Process Complete");  
}

function checkThem1() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rangeList=sh.getRangeList(JSON.parse(PropertiesService.getScriptProperties().getProperty('mystocks1')));
  sh.setActiveRangeList(rangeList);
  ss.toast('Process Complete');
}

function bounceThem1() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const list=sh.getRangeList(JSON.parse(PropertiesService.getScriptProperties().getProperty('mystocks1')));
  let data=[];
  list.getRanges().forEach(function(rg){
    let sA=rg.getValues();//the stored array
    data.push(sA);
    let nA=[];//this is the null array to remove data
    sA.forEach(function(r,i){
      nA[i]=[];
      r.forEach(function(c,j){
        nA[i][j]='';
      });
    });
    rg.setValues(nA);//removing data
  });
  SpreadsheetApp.flush();//insure all data is visible on the sheet
  Utilities.sleep(5000);//5 second bounce delay
  list.getRanges().forEach(function(r,i){r.setValues(data[i]);});//Replacing all data as 2d arrays
  SpreadsheetApp.flush();//Making sure data is complete and visible
  ss.toast("Process Complete");
}

Answer 3

Dependiendo de cuántas columnas tenga, simplemente grabaría una macro de la acción (Herramientas> Macros> Grabar macro) y, una vez completa, siga las instrucciones en la respuesta publicada en la pregunta a continuación para automatizar su actualización con la frecuencia que desee. correr

¿Es posible automatizar los scripts de hojas de cálculo de Google (por ejemplo, sin un evento que los active)?