1
votes

I am not a coder, but I am hacking my way through a Google Apps script to import data from the fitness app Strava to a Google sheet and manipulate that data for analysis. I have managed to use the Strava API and Google sheets API to import the data. However, now, I need to find a way to sort my data so I can analyze it. Since the Strava API appends a new line with each fitness activity, it appears I must duplicate the data (values) into a new sheet. I've tried using formulas to pull the data from the import sheet, but it doesn't seem to work once sorted. Therefore, one solution that comes to mind is to use the spreadsheets.values collection in the Sheets API to add data to a new sheet. THis leads to two questions:

  1. Is there a best practice for filtering dynamic data?

  2. Assuming the approach above is reasonable, what would the code look like? The examples Google gives in the Sheets API documentation do not include Apps Scripts, and I'm not a coder, so I'm looking for help. Here's what I've written thus far. "rangeNamefrom" is the sheet that receives the Strava data."rangeNameto" is the sheet where I want to place the data as values. Any help would be greatly appreciated. Thanks in advance.

/**
 * Creates a Sheets API service object and transfers data from one tab to another of the following spreadsheet as values
 *  https://docs.google.com/spreadsheets/d/10mCWosPmxW71WtuHDtsfpfVU5459NJ24IRbHtxYwoCI/edit#gid=2030256469
 */
function TransferValues() {
  var spreadsheetId = '10mCWosPmxW71WtuHDtsfpfVU5459NJ24IRbHtxYwoCI';
  var rangeNamefrom = 'ProcessedData!A2:AZ1000';
  var rangeNameto = 'ViewTabs!A2:AZ1000';
  var values = SpreadsheetApp.values.batchGet(spreadsheetId, rangeNamefrom).values;

  if (!values) {
    Logger.log('No data found.');
  } else {
    SpreadsheetApp.values.batchUpdate(spreadsheetId, rangeNameto).values;
   }
 }
1

1 Answers

0
votes

The batchUpdate method is not available with the SpreadsheeetApp service. You need to switch to the advanced Sheets service as described in the documentation.

You'll also have to enable the Google Sheets API in the associated console project.