Google sheets scripting copy an array into a sheet

While working with spreadsheets, many times you come across a situation where you have to perform data entry activities using a program rather than doing it manually.

In this article, we will see how to copy an array into a sheet in google sheets.

Copy an array into a sheet using scripting 

Let’s say you have an array like the below which represents the user account data in an e-commerce website.

[{
     name: 'Mark Wine',
     status: 'Active',
     profile_picture: 'https://www.url1.com',
     address: '16 Main Street',
     phone: '(123) 456-789',
     email: '[email protected]'
   },
 
   {
     name: 'Steve Smith',
     status: 'Active',
     profile_picture: 'https://www.url2.com',
     address: '23 North Street',
     phone: '(987) 654-321',
     email: '[email protected]'
   }
 ]

Like this, you have data of thousands of users. You want to copy this data into a google sheet spreadsheet.

Below is the desired output:

Here is the script to copy an array into a sheet

function myFunction() {
  // Set the array to a variable
 var users = [{
     name: 'Mark Wine',
     status: 'Active',
     profile_picture: 'https://www.url1.com',
     address: '16 Main Street',
     phone: '(123) 456-789',
     email: '[email protected]'
   },
 
   {
     name: 'Steve Smith',
     status: 'Active',
     profile_picture: 'https://www.url2.com',
     address: '23 North Street',
     phone: '(987) 654-321',
     email: '[email protected]'
   }
 ];
 
 // Set a headings array which will hold column headings in the order you want
 var headings = ['name', 'status','profile_picture','address', 'phone', 'email'];
 //Initiate an empty output array
 var outputRows = [];
 
 // Loop through users array to extract key/value pairs and after maping with headings array insert them to ouputRows
 users.forEach(function(user) {
   // Add a new row to the output mapping each header to the corresponding member value.
   outputRows.push(headings.map(function(heading) {
     return user[heading] || '';
   }));
 });
 
 //Finally insert outputRow array elements to the actual spreadsheet
 if (outputRows.length) {
   // Add the headings - delete this next line if headings not required
   outputRows.unshift(headings);
   SpreadsheetApp.getActiveSheet().getRange(1, 1, outputRows.length, outputRows[0].length).setValues(outputRows);
 }
}

Copy this script and paste it into an empty editor window.

1. First open a new script window by clicking on Extensions > App script from the menu.

2. Clear any default script on the screen. Now, copy and paste the above script.

3. Click on the Save icon on the menu bar to save the code. Then click on the Run button from menu to execute the code.

If google sheets asks for permissions, pls allow it.

Below are the steps to allow the permissions.

After the execution finishes, you will see the array values will appear in the spreadsheet.

Here is a screenshot showing the same

Let me explain the script.

First, you will assign the array to a variable called “users”.

Next, you will create an array with the name of column headings. Assign this array to a variable called “headings”.

Now initiate an empty array called “outputRows” which will hold all the google sheets rows in the future.

The next step is to fill this empty array with desired rows. To do this, you need to loop through the “users” array and insert the individual key-value pairs from the “users” array while mapping the key with the headings array. This way all the column headings will have their respective values from the “users” array.

In the next function, you will check if the empty array is now filled (by ensuring the length is >0) and then insert the values into the current active spreadsheet.

Extract data from sheets to an array in google sheets

In the above example, you saw how to copy an array into a sheet using scripting in google sheets.

Now let’s see how to do the opposite. This means you have a few data in a sheet that you want to extract as an array.

Example data that we want to extract from google sheets to an array:

Note that we have typed the same data from the previous example into a new sheet.

Here is the script to extract the data from google sheets and print on App script window:

function getDataFromSheet()
{
 var sheet = SpreadsheetApp.getActiveSheet(); // get active sheet
 var range = sheet.getDataRange(); // get range object containing data
 var data = range.getValues(); // write range data into array called data
 Logger.log(data); // Log result
}

Here is how the final output will look like

Explanation of the script:

1. First get the active sheet reference. 

2. Then get the entire data range. Here you can specify if you want only for a certain cell range.

3. Then get the values from the range using the getValues() method

4. This will get the data and put it in an array

5. Finally log this array to see the result

Wrapping up

In this article, you learned about google sheets scripting to copy an array into a sheet. Also, you learned how to extract data from a sheet and copy it into an array.

Now it’s your time to try these on your worksheet. Let me know in the comments if you have any doubts or questions.

Appendix

[1] App script in google sheets – Link

Further Reading

New to google sheets ? Start here