วิธีลบเซลล์และป้อนรายละเอียดเดิมอีกครั้งใน 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 sheet script

ขอขอบคุณ


แก้ไข: นี่คือสำเนาของแผ่นงาน Google ที่ฉันใช้งานได้

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

โปรดลองทำสำเนา

ขอบคุณอีกครั้งสำหรับความช่วยเหลือ

คำตอบ

2 Tanaike Jan 12 2021 at 12:18

จากวิดีโอตัวอย่างของคุณอยู่ในสถานการณ์ของคุณผมคิดว่าเมื่อค่าของเซลล์ "B9" และ "B10" จะรวมอยู่ในurlและxpathของสูตร=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

ฟังก์ชันด้านบนช่วยให้คุณสามารถเลือกเซลล์ทั้งหมดที่คุณต้องการตีกลับ ใช้ปุ่มควบคุมและเลือกเซลล์ทั้งหมดจากนั้นเรียกใช้ 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');
}

ตอนนี้คุณสามารถเรียกใช้ounceThem () แล้วค่าจะหายไปและปรากฏขึ้นอีกครั้ง

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

ฉันพบว่าสิ่งนี้ค่อนข้างทำงานดังนั้นฉันจึงกลับมาในเช้าวันนี้และตั้งค่าให้ใช้ setValues ​​() แทน setValue () และตอนนี้การตีกลับเร็วขึ้นมาก

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 Spreadsheets Scripts เป็นไปโดยอัตโนมัติ (เช่นไม่มีเหตุการณ์ที่จะเรียกใช้)