Reading and updating multiple tabs in the same spreadsheet

Question

I have a Google Spreadsheet where I retrieve data using the Google Analytics addon. In a tab I combine all the data and use this specific tab for my Google Datastudio report. In here I analyze my Google Ads campaign data - the costs and the conversions.

This Spreadsheet has multiple tabs (each tab contains a specific conversion) I need to be able to read and write for multiple tabs. It has to read the Google Analytics data and write this data in another sheet. I originally created this script to read from 1 tab and to write to 1 tab and it was working. Then I noticed I will need to do this for almost all the tabs.

This is my current script:

function readAndWrite() {
    var spreadsheetId = 'xxx';
    var counter = 0;
    var rangeName = ['readingTab1!A16:C','readingTab2!A16:C','readingTab3!A16:C','readingTab4!A16:C','readingTab5!A16:C'];
    var rangePush = ['writingTab1','writingTab2','writingTab3','writingTab4','writingTab5','writingTab5'];
    var values = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeName[counter]).values;
    var data = [];
  
    if (!values) {
      Logger.log('No data found.');
    } else {
      Logger.log('Starting script.');
      Logger.log('There are ' + values.length + ' unique rows.');
      Logger.log(values[0][2]);

while (counter < 5){
  data.length = 0;

  for (var row = 0; row < values.length; row++) {
      var campaign = values[row][0];
      var date = values[row][1];
      var cost = values[row][2];
      data.push({range: rangePush[counter], values: [[date, campaign, cost]]});
      }
counter++;
    
    Sheets.Spreadsheets.Values.batchUpdate({data: data, valueInputOption: "USER_ENTERED"},spreadsheetId);
    }
    
    }
    Logger.log('Finished.');
    
}

In the rangeName I have created an array with the names and ranges of my tab where it should read from. In the rangePush I created an array with the names where it should push the data to.

When I run the script I receive the following error:

GoogleJsonResponseException: API call to sheets.spreadsheets.values.batchUpdate failed with error: Invalid data[1886]: Unable to parse range: writingTab2.

Is there anyone who can see what is going wrong and able to help me out here? I hope my explanation is clear, if not please let me know.

Thanks!

Answer 1

I believe your goal is as follows.

• You want to copy the values from readingTab#!A16:C sheet to writingTab# sheet using Sheets API with Google Apps Script.
  • Those sheet names are put to the arrays and each index of both is corresponding to the copy and paste sheets.

If my understanding is correct, how about the following modification?

At first, about your error, when I saw your sample Spreadsheet, there are 3 read and write sheets of 'readingTab1!A16:C', 'readingTab2!A16:C', 'readingTab3!A16:C' and 'writingTab1', 'writingTab2', 'writingTab3'. But your script uses while (counter < 5){. By this, the range becomes undefined after counter is more than 4. I thought that this might be the reason of your issue.

In order to achieve your goal by removing this issue, how about the following modified script?

Modified script:

When spreadsheets.values.batchGet and spreadsheets.values.batchUpdate methods are used, the script becomes the following simple script.

function readAndWrite() {
  var spreadsheetId = 'xxx';
  var rangeName = ['readingTab1!A16:C', 'readingTab2!A16:C', 'readingTab3!A16:C'];
  var rangePush = ['writingTab1', 'writingTab2', 'writingTab3'];

  var values = Sheets.Spreadsheets.Values.batchGet(spreadsheetId, {ranges: rangeName}).valueRanges;
  values.forEach((v, i) => v.range = rangePush[i]);
  Sheets.Spreadsheets.Values.batchUpdate({ data: values, valueInputOption: "USER_ENTERED" }, spreadsheetId);
}

• In this modified script, it supposes that there are 3 read and write sheets of 'readingTab1!A16:C', 'readingTab2!A16:C', 'readingTab3!A16:C' and 'writingTab1', 'writingTab2', 'writingTab3'. When you add more sheets, please add them to the arrays of rangeName and rangePush.

Note:

• In this script, please check the values of rangeName and rangePush and the sheet names of your Spreadsheet again.

• This script uses Sheets API. So, please enable Sheets API at Advanced Google services.

• I think that in your situation, the Spreadsheet service instead of Sheets API can also achieve your goal. But I think that the process cost will be lower when Sheets API is used. But, when you want to achieve your goal without using Sheets API, you can also use the following script.

    function readAndWrite2() {
      var spreadsheetId = 'xxx';
      var rangeName = ['readingTab1!A16:C', 'readingTab2!A16:C', 'readingTab3!A16:C'];
      var rangePush = ['writingTab1', 'writingTab2', 'writingTab3'];
  
      var ss = SpreadsheetApp.openById(spreadsheetId);
      rangeName.forEach((r, i) => ss.getRange(r).copyTo(ss.getSheetByName(rangePush[i]).getRange("A1")));
    }
  

References:

• Method: spreadsheets.values.batchGet
• Method: spreadsheets.values.batchUpdate