CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jacky
Jacky

Reputation: 43

Anyone can help me to speed up this Google Sheet script?

I am using the following script to refresh my Google Finance watchlist. I am not a programmer so that's why I copied this from somewhere and use it as long as it does the trick. It certainly works for me but it's kinda slow, and I hope someone can help me to optimize this script to make it better.

My understanding is, this script basically scan the whole table (7 columns and over 60 rows) to find out the no. of rows and column. It first empty the cells then fill the cell with original formula (which is GoogleFinance function for stock quote, something like that) to force the update for the whole table. I actually just need to trigger update for a specific column but I don't know how to do it (yep, not a programmer..). I tried to change the row = x and col = x to another values and it would help to reduce the scope of the update size of the table, and it help me abit only.

In order to make it update more frequently, I actually copy and paste those two for loops set 5 more times and use Utilities.sleep(10000); in between to make the script run 6 rounds a minute. So I can bypass the minimum 1 minute interval in Google Sheet setting. But yes, I know it's cause the script run even slower...

I hope someone can help me to speed this script up, really appreciate anyone can help! Thanks you!!


function forceRefreshSheetFormulas(sheetName) {
    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = activeSpreadsheet.getSheetByName('*Table Name*');
    var range = sheet.getDataRange();
    var numCols = range.getNumColumns();
    var numRows = range.getNumRows();
    var rowOffset = range.getRow();
    var colOffset = range.getColumn();

    // Change formulas then change them back to refresh it
    var originalFormulas = range.getFormulas();

    //Loop through each column and each row in the sheet
    //`row` and `col` are relative to the range, not the sheet
    for (row = 0; row < numRows ; row++){
    for(col = 0; col < numCols; col++){
    if (originalFormulas[row][col] != "") 
    range.getCell(row+rowOffset, col+colOffset).setFormula("");
    }
    };
    };
    SpreadsheetApp.flush();
    for (row = 0; row < numRows ; row++){
    for(col = 0; col < numCols; col++){
    if (originalFormulas[row][col] != "") {
    range.getCell(row+rowOffset, col+colOffset).setFormula(originalFormulas[row][col]);
    }
    };
    };
    SpreadsheetApp.flush();

Upvotes: 0

Views: 101

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15318

Try

pls enable Sheets API at Advanced Google services.

function forceRefreshSheetFormulas(sheetName) {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = activeSpreadsheet.getSheetByName('*Table Name*');
  var range = sheet.getDataRange();
  var numCols = range.getNumColumns();
  var numRows = range.getNumRows();
  var rowOffset = range.getRow();
  var colOffset = range.getColumn();

  // Change formulas then change them back to refresh it
  var originalFormulas = range.getFormulas();

  //Loop through each column and each row in the sheet
  //`row` and `col` are relative to the range, not the sheet
  var ranges = []
  var blank = []
  var original = []
  for (row = 0; row < numRows; row++) {
    for (col = 0; col < numCols; col++) {
      if (originalFormulas[row][col] != ""){
        ranges.push( `'*Table Name*'!${columnToLetter(col + colOffset)}${row + rowOffset}` )
        blank.push('')
        original.push(`${originalFormulas[row][col]}`)
      };
    }
  };
  updateGoogleSheet(ranges,blank)
  SpreadsheetApp.flush();
  updateGoogleSheet(ranges,original)
};
function updateGoogleSheet(ranges,values) {
  var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId()
  var data = ranges.map((e, i) => ({ range: e, values: [[values[i]]] }));
  Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, spreadsheetId);
}
function columnToLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

reference

batchupdate

Upvotes: 1

Related Questions