셀을 삭제하고 Google 스프레드 시트에 동일한 세부 정보를 다시 입력하는 방법

Jan 12 2021

현재 주가 데이터 가져 오기를 자동화하는 방법을 만들고 있습니다 ..

처음에는 셀 값을 업데이트하려면 셀의 importxml 문을 새로 고치면된다고 생각했습니다. 그래서 주기적으로 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();
  }
}

분명히 셀을 업데이트하지 않습니다. 그래서 셀을 새로 고치려면

  1. 주식 명 삭제
  2. 주식 이름을 다시 입력하십시오

여기 내가 그 의미를 보여주는 비디오가 있습니다 (단지 명확하게하기 위해) https://streamable.com/eciks0

Google 시트 스크립트를 사용하여 어떻게 자동화 할 수 있습니까?

감사합니다


편집 : 여기에 Google 시트의 사본이 있습니다.

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

복사본을 만들어보십시오.

도움을 주셔서 다시 한번 감사드립니다

답변

2 Tanaike Jan 12 2021 at 12:18

샘플 비디오에서 귀하의 상황에서 셀 "B9"및 "B10" 의 값이 urlxpath에 포함 =IMPORTXML(url, xpath)되면 셀 "B1"의 값이 변경되면 수식이 새로 고쳐 진다고 생각했습니다. 그렇다면 다음 샘플 스크립트는 어떻습니까?

샘플 스크립트 :

다음 스크립트를 복사하여 Google 스프레드 시트의 스크립트 편집기에 붙여넣고의 기능을 실행하십시오 myFunction. 이 샘플 스크립트에서 "B1"셀의 값을으로 덮어 씁니다 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);
}
  • 위의 스크립트가 유용하지 않고 질문에 스크립트를 사용하려는 경우 다음 스크립트를 사용할 수 있습니다. 이 경우 var id = "YOUR-SHEET-ID";실제 상황에 맞게 설정 하십시오.

      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
      }
    
  • 또는 시트의 수식을 새로 고치는 데 다음 스크립트를 사용할 수 있다고 생각합니다.

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

참조 :

  • 관련 스레드
    • 사용자 정의 함수 및 재 계산
    • 함수 NOW () + 시간대 설정 (Google 스프레드 시트)

추가 :

답장과 공유 동영상에서 다음 샘플 스크립트는 어떻습니까? 이 경우 간단한 스크립트로 셀을 지우고 값을 acen다시 입력합니다.

샘플 스크립트 :

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

top 기능을 사용하면 바운스하려는 모든 셀을 선택할 수 있습니다. Ctrl 키를 사용하여 모든 셀을 선택한 다음 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");  
}

그것들을 얻은 후에 화면을 클릭하여 선택을 지울 수 있습니다. checkThem () 실행 및 선택 사항은 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');
}

이제 bounceThem ()을 실행할 수 있으며 값이 사라졌다가 다시 나타납니다.

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

나는 이것이 오히려 실행된다는 것을 알았으므로 오늘 아침에 돌아와 setValue () 대신 setValues ​​()를 사용하도록 설정했으며 지금 바운스를 수행하는 것이 훨씬 빠릅니다.

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

얼마나 많은 열이 있는지에 따라 작업의 매크로 (도구> 매크로> 매크로 기록)를 기록하고 완료되면 아래 질문에 게시 된 답변의 지침에 따라 원하는만큼 자주 새로 고침을 자동화합니다. 달리다

Google 스프레드 시트 스크립트를 자동화 할 수 있습니까 (예 : 스크립트를 트리거하는 이벤트없이)?