Cách xóa một ô và nhập lại các chi tiết tương tự trong Google Trang tính

Jan 12 2021

tôi hiện đang thực hiện một cách để tự động hóa việc tìm nạp dữ liệu về giá cổ phiếu ..

lúc đầu, tôi nghĩ rằng để cập nhật giá trị của ô, tôi chỉ cần làm mới câu lệnh importxml trong ô. vì vậy tôi đã sử dụng mã này từ hàm làm mới định kỳ bảng tính 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();
  }
}

rõ ràng, nó KHÔNG cập nhật ô .. vì vậy tôi phát hiện ra rằng để làm mới ô, tôi cần

  1. xóa tên cổ phiếu
  2. nhập lại tên cổ phiếu

đây là một video cho thấy ý của tôi về điều đó (chỉ để làm rõ hơn) https://streamable.com/eciks0

làm cách nào để tự động hóa việc này, có thể sử dụng google sheet script?

cảm ơn bạn


CHỈNH SỬA: đây là bản sao của trang tính Google mà tôi đang làm việc với

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

hãy cố gắng tạo một bản sao của nó.

Cảm ơn lần nữa vì sự giúp đỡ của bạn

Trả lời

2 Tanaike Jan 12 2021 at 12:18

Từ video mẫu của bạn, trong tình huống của bạn, tôi nghĩ rằng khi giá trị của các ô "B9" và "B10" được bao gồm trong urlxpathcủa công thức =IMPORTXML(url, xpath), khi giá trị của ô "B1" được thay đổi, công thức sẽ được làm mới. Vì vậy, làm thế nào về kịch bản mẫu sau đây?

Kịch bản mẫu:

Vui lòng sao chép và dán tập lệnh sau vào trình chỉnh sửa tập lệnh của Google Bảng tính và chạy chức năng của myFunction. Trong tập lệnh mẫu này, giá trị của ô "B1" bị ghi đè 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);
}
  • Nếu tập lệnh trên không hữu ích và khi bạn muốn cũng sử dụng tập lệnh của mình trong câu hỏi của mình, bạn có thể sử dụng tập lệnh sau. Trong trường hợp này, vui lòng đặt var id = "YOUR-SHEET-ID";cho tình huống thực tế của bạn.

      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
      }
    
  • Hoặc, tôi nghĩ rằng bạn có thể sử dụng tập lệnh sau để làm mới các công thức trong trang tính.

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

Người giới thiệu:

  • Chủ đề liên quan
    • Chức năng tùy chỉnh và tính toán lại
    • Hàm NOW () + Đặt múi giờ (Google Trang tính)

Thêm:

Từ câu trả lời của bạn và video được chia sẻ của bạn, còn đoạn mã mẫu sau đây thì sao? Trong trường hợp này, như một tập lệnh đơn giản, ô sẽ bị xóa và đặt lại giá trị acen.

Kịch bản mẫu:

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 và BouncEm

Chức năng trên cùng cho phép bạn chọn tất cả các ô mà bạn muốn trả lại. Sử dụng phím điều khiển và chọn tất cả các ô, sau đó chạy 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");  
}

Sau khi có chúng, bạn có thể nhấp vào màn hình để xóa các lựa chọn của mình. Chạy checkThem () và các lựa chọn của bạn sẽ xuất hiện lại vì chúng đáng lẽ đã được lưu trong 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');
}

Bây giờ bạn có thể chạy bounceThem () và các giá trị sẽ biến mất và sau đó xuất hiện lại

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

Tôi thấy điều này khá chạy vì vậy tôi đã quay lại sáng nay và thiết lập nó để sử dụng setValues ​​() thay vì setValue () và hiện tại việc thoát nhanh hơn nhiều.

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

Tùy thuộc vào số lượng cột bạn có, tôi chỉ cần ghi lại một macro của hành động (Công cụ> Macro> Ghi Macro) và sau khi hoàn tất, hãy làm theo hướng dẫn trong câu trả lời được đăng trong câu hỏi bên dưới để tự động làm mới nó bất cứ lúc nào bạn muốn. chạy

Có thể tự động hóa Tập lệnh bảng tính của Google (ví dụ như không có sự kiện để kích hoạt chúng) không?