Come eliminare una cella e reinserire gli stessi dettagli in Fogli Google

Jan 12 2021

Attualmente sto cercando di automatizzare il recupero dei dati del prezzo delle azioni ..

all'inizio, ho pensato che per aggiornare il valore della cella, ho solo bisogno di aggiornare l'istruzione importxml nella cella. quindi ho usato questo codice dalla funzione di aggiornamento periodico del foglio di calcolo IMPORTXML ()

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

a quanto pare, NON aggiorna la cella .. così ho scoperto che per aggiornare la cella, ho bisogno di

  1. eliminare il nome dello stock
  2. reinserire il nome dello stock

ecco un video che mostra cosa intendevo con questo (solo per chiarire) https://streamable.com/eciks0

come posso automatizzarlo, magari usando lo script del foglio di google?

grazie


EDIT: ecco una copia del foglio google con cui sto lavorando

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

per favore prova a farne una copia.

grazie ancora per l'aiuto

Risposte

2 Tanaike Jan 12 2021 at 12:18

Dal tuo video di esempio, nella tua situazione, ho pensato che quando i valori delle celle "B9" e "B10" sono inclusi in urle xpathdella formula =IMPORTXML(url, xpath), quando il valore della cella "B1" viene modificato, la formula viene aggiornata. Quindi, che ne dici del seguente script di esempio?

Script di esempio:

Copia e incolla il seguente script nell'editor di script di Google Spreadsheet ed esegui la funzione di myFunction. In questo script di esempio, il valore della cella "B1" viene sovrascritto da 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);
}
  • Se lo script sopra non è utile e quando vuoi usare anche il tuo script nella tua domanda, puoi usare lo script seguente. In questo caso, imposta la var id = "YOUR-SHEET-ID";tua situazione attuale.

      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
      }
    
  • Oppure, penso che potresti essere in grado di utilizzare il seguente script per aggiornare le formule nel foglio.

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

Riferimenti:

  • Discussioni correlate
    • Funzioni personalizzate e ricalcolo
    • Funzione NOW () + Impostazione del fuso orario (Fogli Google)

Aggiunto:

Dalla tua risposta e dal tuo video condiviso, che ne dici del seguente script di esempio? In questo caso, come un semplice script, la cella viene svuotata e viene rimesso il valore di acen.

Script di esempio:

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);
}
1 Cooper Jan 12 2021 at 15:29

GetEmCheckEm e BouncEm

La funzione in alto ti consente di selezionare tutte le celle che vuoi far rimbalzare. Usa il tasto Control e seleziona tutte le celle, quindi esegui 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");  
}

Dopo averli ottenuti, puoi fare clic sullo schermo per cancellare le selezioni. L'esecuzione di checkThem () e le selezioni dovrebbero riapparire perché avrebbero dovuto essere salvate in 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');
}

Ora puoi eseguire bounceThem () ei valori scompariranno e poi riappariranno

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

L'ho trovato piuttosto eseguito, quindi sono tornato questa mattina e l'ho impostato per usare setValues ​​() invece di setValue () ed è molto più veloce fare il rimbalzo ora.

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

WaveChappelle Jan 12 2021 at 12:11

A seconda di quante colonne hai, registrerei semplicemente una macro dell'azione (Strumenti> Macro> Registra macro) e una volta completato segui le istruzioni nella risposta pubblicata nella domanda seguente per automatizzare il suo aggiornamento con la frequenza che desideri correre

È possibile automatizzare gli script di Google Spreadsheets (ad es. Senza un evento per attivarli)?