Google Sheets Macro not pasting formula

Question

Please help with Google sheets macro. Trying simple stuff as a start. Macro needs to clear B2 cell and paste formula (importhtml) as shown below. Then it has to do the same for cell B43 (Potentially will do this in multiple cells on this sheet if it ever works).

Google sheets link: https://docs.google.com/spreadsheets/d/1O0AJJEd0tGyFXlEPhih8HG9YOWcZNRot_Z7FXPdQrx8/edit?usp=sharing

Strangely it works in B43, but leaves B2 empty.

 function Untitledmacro() {

// Clear B2 and then paste formula

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('DATA'), true);
  spreadsheet.getRange('B2').activate();

  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('DATA'), true);
  spreadsheet.getRange('B2').activate();

  spreadsheet.getCurrentCell().setFormula('=IMPORTHTML("https://www.investing.com/commodities/real-time-futures","table",1)');

// Clear B43 and then paste formula


  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('DATA'), true);
  spreadsheet.getRange('B43').activate();

  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('DATA'), true);
  spreadsheet.getRange('B43').activate();

  spreadsheet.getCurrentCell().setFormula('=IMPORTHTML("https://www.investing.com/indices/indices-futures","table",1)');



  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('DATA'), true);

Added Sheets API, not sure it's needed here though

Hi David, we can't access the spreadsheet you shared. Please fix permissions. — JPV

JPV JPV · Accepted Answer · 2021-03-05T09:31:40

See if this helps

function clearAndSetformula() {

  const sheet = SpreadsheetApp.getActive().getSheetByName('DATA');
  const cells = ["B2", "B43"];
  const formulas = [
    '=IMPORTHTML("https://www.investing.com/commodities/real-time-futures","table",1)',
    '=IMPORTHTML("https://www.investing.com/indices/indices-futures","table",1)'
  ];
  cells.forEach((cell, i) => sheet.getRange(cell).clear({ contentsOnly: true, skipFilteredRows: true }).setFormula(formulas[i]));
  };

If you need to add more cells/formulas, add them in the proper arrays. Make sure the positions line up so that the first cell in the array cells will have the first formula in the array formulas etc...

I hope that helps?

Google Sheets Macro not pasting formula

1 Answers