How to Update Google Spreadsheets With The Latest Available Data

/How to Update Google Spreadsheets With The Latest Available Data
How to Update Google Spreadsheets With The Latest Available Data2017-10-06T10:50:20+00:00

Create the Google Spreadsheet

The data in a Google Spreadsheet can easily be refreshed.

You can copy any qualified spreadsheet to Google Drive as a Google Spreadsheet.  Your immediate question would be: “What does qualified mean?”

  1. Use NEXT Analytics Excel Addin to create an Excel file and copy that file to Google Spreadsheets
  2. Your account on NEXT Analytics Web Service will send spreadsheets to you by email which you can copy to Google Spreadsheets
  3. When you refresh data from your web app, if you have authorized access to your Google Drive, then it offers to copy the Excel file to a Google Spreadsheet.

You have four choices to refresh the data in a Google Spreadsheet

If you don’t want people from initiating their own data refreshes, you can make it so that it only refreshes if you ask for it.

Login to:
Click the menu item that says Google Spreadsheet.
Enter the Google spreadsheet ID
Click a Start Download and the rest is automatic.

The Google Spreadsheet will soon have the latest data in it.

If you wish to skip the need to login…

Customize this URL (replace the parts that are bolded with your own information[your email id]&PSW=[your nextanalytics password]&GSSID=[id of the google spreadsheet]

Paste the URL into the address bar of your browser
Press Enter

Download the following html file to your computer 

View the sample html file

Edit the file to customize the login credentials and the Google Spreadsheet ID

Advantages of using this approach:

  • requests are never cached
  • requests do not remain in the browser history
  • requests cannot be bookmarked

In order to refresh the data in the Google Spreadsheet, you can use the edited version of this file and do the following:

  1. Double click the file or
  2. Invoke it with a scheduler or
  3. Invoke it as an anchor tag in a web site or
  4. Add the URL to a cell in Excel or
  5. Put the file onto a web server, and refer to its URL in a cell in any spreadsheet (the same or a different Google spreadsheet)
  6. Add the URL to any document or Shortcut on your desktop computer or Google Doc

Open the Google Spreadsheet  where you want a custom menu added.

Click the Tools menu.

Choose the menu item that says “Script Editor”

A new tab will open. Copy/paste the following script into it.

function function1(){ var url = “”; var ss = SpreadsheetApp.getActiveSpreadsheet(); var user = Session.getActiveUser().getEmail() ; var ui = SpreadsheetApp.getUi(); var response = ui.prompt( ‘Password for Downloading Data using NEXT Analytics’,’Check your email inbox, and enter the code that was sent to you’, ui.ButtonSet.OK_CANCEL ); if( response.getSelectedButton() == ui.Button.OK ){ var psw = response.getResponseText(); var data = { ‘ACC’: user, ‘PSW’: psw, ‘GSSID’: ss.getId() }; var options = { ‘method’ : ‘post’, ‘payload’: data }; UrlFetchApp.fetch( url, options ); } } // The onOpen function is executed automatically every time a Spreadsheet is loaded function onOpen() { var menuEntries = []; menuEntries.push({name: “Refresh This Spreadsheet”, functionName: “function1”}); var ss = SpreadsheetApp.getActiveSpreadsheet(); ss.addMenu(“NEXT Analytics Web”, menuEntries); }

Reload (refresh) the Google Spreadsheet

The Google Spreadsheet will now have a new menu item

Click the new menu item

Answer the prompt about your NEXT Analytics password

The Google Spreadsheet will be refreshed.