CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Shan Jiang
Shan Jiang

Reputation: 57

How to loop through a list of values and export the outputs to a different google sheets

In google sheets, I have a "Source" tab where cell "A1" can be selected from a list of 5 values from the range "A1: A5" on a different tab called "Data". Range "B1: D3" on the same "Source" tab is dynamic upon what the value "A1" is selected to be.

What I want is to loop through all the five values for "A1" and copy each set of the values from the range B1:D3 (9 cells), and then stack them in another google sheet id: 123456 and tab "sheet1".

Upvotes: 1

Views: 235

Answers (1)

Marios
Marios

Reputation: 27348

Solution:

You can do that with Google Apps Script.

Try this:

function myFunction() {
  
  const ss = SpreadsheetApp.getActive();
  const source_tab = ss.getSheetByName('Source');
  const output_tab = ss.getSheetByName('sheet1');
  const data_vals = ss.getSheetByName('Data').getRange('A1:A5').getValues().flat(1);
  var source_data;
  data_vals.forEach(val=>{
                    
       source_tab.getRange('A1').setValue(val);
       SpreadsheetApp.flush();
       source_data=source_tab.getRange('B1:D3').getValues();
       output_tab.getRange(output_tab.getLastRow()+1,1,source_data.length,source_data[0].length).setValues(source_data);
                    });  
}

Instructions:

  1. Click on Tools => Script editor:

step1

  1. Copy/Paste the aforementioned code snippet into the script editor and click on the run (play) button:

step2

When you execute the function for the first time you will be asked to allow the script to interact with your spreadsheet file. Then you just need to accept and allow access.

Upvotes: 1

Related Questions