Como excluir uma célula e inserir novamente os mesmos detalhes no Planilhas Google

Jan 12 2021

No momento, estou criando uma maneira de automatizar a obtenção de dados sobre o preço das ações.

No início, pensei que para atualizar o valor da célula, eu só precisava atualizar a instrução importxml na célula. então usei este código da função Atualizar periodicamente a planilha 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();
  }
}

aparentemente, ele NÃO atualiza o celular .. então eu descobri que para atualizar o celular, eu preciso

  1. exclua o nome do estoque
  2. insira novamente o nome do estoque

aqui está um vídeo mostrando o que eu quis dizer com isso (só para deixar claro) https://streamable.com/eciks0

como posso automatizar isso, talvez usando o script de planilha do google?

obrigada


EDITAR: aqui está uma cópia da planilha do google com a qual estou trabalhando

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

por favor, tente fazer uma cópia dele.

Mais uma vez obrigado pela ajuda

Respostas

2 Tanaike Jan 12 2021 at 12:18

Do seu vídeo de amostra, na sua situação, pensei que quando os valores das células "B9" e "B10" são incluídos urle xpathda fórmula =IMPORTXML(url, xpath), quando o valor da célula "B1" é alterado, a fórmula é atualizada. Então, que tal o exemplo de script a seguir?

Script de amostra:

Copie e cole o seguinte script no editor de scripts do Google Spreadsheet e execute a função de myFunction. Neste script de exemplo, o valor da célula "B1" é substituído por 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 o script acima não for útil e quando você quiser usar também o seu script em sua pergunta, você pode usar o script a seguir. Nesse caso, defina var id = "YOUR-SHEET-ID";para sua situação 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
      }
    
  • Ou, acho que você pode usar o seguinte script para atualizar as fórmulas na planilha.

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

Referências:

  • Tópicos relacionados
    • Funções personalizadas e recálculo
    • Função AGORA () + Definição de fuso horário (Planilhas Google)

Adicionado:

Com base na sua resposta e no vídeo compartilhado, que tal o exemplo de script a seguir? Nesse caso, como um script simples, a célula é limpa e colocada o valor de acennovamente.

Script de amostra:

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

A função superior permite que você selecione todas as células que deseja saltar. Use a tecla de controle e selecione todas as células e execute 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");  
}

Depois de obtê-los, você pode clicar na tela para limpar suas seleções. A execução de checkThem () e suas seleções devem reaparecer porque deveriam ter sido salvas em 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');
}

Agora você pode executar bounceThem () e os valores irão desaparecer e reaparecer

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

Achei isso bastante executado, então voltei esta manhã e configurei para usar setValues ​​() em vez de setValue () e é muito mais rápido fazer o salto agora.

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

Dependendo de quantas colunas você tem, eu simplesmente gravaria uma macro da ação (Ferramentas> Macros> Gravar macro) e, uma vez concluída, seguiria as instruções na resposta postada na pergunta abaixo para automatizar sua atualização com a frequência que você desejar para correr

É possível automatizar os scripts das planilhas do Google (por exemplo, sem um evento para acioná-los)?