User Tools

Site Tools


google

How to send data to Google Sheets?

  1. Create a new folder on your hard drive to be synced to Google Drive
  2. Run Google Backup and Sync tool and choose the folder
  3. In Scoreboard OCR choose “CSV File” output and save the file to that folder
  4. In Google Drive check the content of the .csv file and see if it updates as digits on scoreboard change
  5. In Google Drive make the .csv file publicly availale using Sharing
  6. Copy link to the .csv file in Google Drive. It will look like this (url1):
    https://drive.google.com/file/d/[FILE_ID]/view?usp=sharing
  7. Copy it to notepad and change to ulr2:
    http://drive.google.com/uc?export=view&id=[FILE_ID]
  8. Create a Google Sheet
  9. Choose a cell where to display scoreboard data and paste the following formula using url2:
    =IMPORTDATA("http://drive.google.com/uc?export=view&id=[FILE_ID]")
  10. Go to Tools → Script Editor
  11. Copy paste the following lines:
    function forceRefreshSheetFormulas(sheetName) {
      var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = activeSpreadsheet.getSheetByName("Lapa1");
      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();
    };
  12. Save the script and give it a name
  13. Run the script and see if there are no errors
  14. Set up a trigger for the scirpt to run 'Time-driven' and 'Every-minute'

Here is a helpful tutorial for setting up script on Google Sheets
Total delay is 30sec - 1,5min

google.txt · Last modified: 2021/02/11 19:54 by arturs