セルを削除して同じ詳細を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();
  }
}

どうやら、それはセルを更新しません..だから私はセルを更新するために、私はする必要があることを発見しました

株名を削除する
株名を再入力してください

これは私がそれが何を意味したかを示すビデオです（明確にするために） https://streamable.com/eciks0

おそらくグーグルシートスクリプトを使用して、これを自動化するにはどうすればよいですか？

ありがとうございました

編集：これは私が働いているグーグルシートのコピーです

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

これで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スプレッドシートスクリプトを自動化することは可能ですか（たとえば、スクリプトをトリガーするイベントなしで）？