How to delete a cell and reenter the same details in Google Sheets

Question

i am currently making a way to automate the data fetching of stock price..

at first, i thought that to update the value of the cell, i just need to refresh the importxml statement in the cell. so i used this code from Periodically refresh IMPORTXML() spreadsheet function

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

apparently, it DOES NOT update the cell.. so i discovered that to refresh the cell, i need to

1. delete the name of the stock
2. re-enter the name of the stock

here is a video showing what i meant by that (just to make it clear) https://streamable.com/eciks0

how can i automate this, maybe using the google sheet script?

thank you

---

EDIT: here is a copy of the google sheet im working with

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

please try to make a copy of it.

thank you again for the help

Answer 1

From your sample video, in your situation, I thought that when the values of the cells "B9" and "B10" are included in url and xpath of the formula =IMPORTXML(url, xpath), when the value of cell "B1" is changed, the formula is refreshed. So, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and run the function of myFunction. In this sample script, the value of cell "B1" is overwritten by 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);
}

• If above script is not useful and when you want to also use your script in your question, you can use the following script. In this case, please set var id = "YOUR-SHEET-ID"; for your actual situation.

    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
    }
  

• Or, I think that you might be able to use the following script for refreshing the formulas in the sheet.

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

References:

• Related threads
  • Custom Functions and Recalculation
  • Function NOW() + Setting Timezone (Google Sheets)

Added:

From your replying and your shared video, how about the following sample script? In this case, as a simple script, the cell is cleared and put the value of acen again.

Sample script:

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 And BouncEm

The top function allows you to select all of the cells that you want to bounce. Use the control key and select all of the cells and then run 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");  
}

After you've got them you can click on the screen to clear your selections. The run checkThem() and your selections should reappear because they should have been saved 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');
}

Now you can run bounceThem() and the values will disappear and then reappear

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

I found this rather run so I came back this morning and set it up to use setValues() instead of setValue() and it's much faster doing the bounce now.

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

Depending on how many columns you have I'd simply record a macro of the action (Tools>Macros>Record Macro) and once complete follow the instructions in the answer posted in the question below to automate its refresh however often you'd like it to run

Is it possible to automate Google Spreadsheets Scripts (e.g. without an event to trigger them)?