Unpivot In Google Sheets With Formulas (How To Turn Wide Data Into Tall Data)

Unpivot in Google Sheets is a method to turn “wide” tables into “tall” tables, which are more convenient for analysis.

Suppose we have a wide table like this:

Wide Data Table

Wide data like this is good for the Google Sheets chart tool but it’s not ideal for creating pivot tables or doing analysis. The main reason is that data is captured in the column headings, which prevents you using it in pivot tables for analyis.

So we want to transform this data — unpivot it — into the tall format that is the way databases store data:

Unpviot in Google Sheets

But how do we unpivot our data like that?

It turns out it’s quite hard.

It’s harder than going the other direction, turning tall data into wide data tables, which we can do with a pivot table.

This article looks at how to do it using formulas so if you’re ready for some complex formulas, let’s dive in…

Unpivot in Google Sheets

We’ll use the wide dataset shown in the first image at the top of this post.

The output of our formulas should look like the second image in this post.

In other words, we need to create 16 rows to account for the different pairings of Customer and Product, e.g. Customer 1 + Product 1, Customer 1 + Product 2, etc. all the way up to Customer 4 + Product 4.

Of course, we’ll employ the Onion Method to understand these formulas.


Click here to open the Unpivot in Google Sheets template

Feel free to make your own copy (File > Make a copy…).

(If you can’t open the file, it’s likely because your G Suite account prohibits opening files from external sources. Talk to your G Suite administrator or try opening the file in an incognito browser.)

Step 1: Combine The Data

Use an array formula like this to combine the column headings (Customer 1, Customer 2, etc.) with the row headings (Product 1, Product 2, Product 3, etc.) and the data.

It’s crucial to add a special character between these sections of the dataset though, so we can split them up later on. I’ve used the fox emoji (because, why not?) but you can use whatever you like, provided it’s unique and doesn’t occur anywhere in the dataset.


The output of this formula is:

Unpivot Data In Google Sheets Step 1

Step 2: Flatten The Data

Before the introduction of the FLATTEN function, this step was much, much harder, involving lots of weird formulas.

Thankfully the FLATTEN function does away with all of that and simply stacks all of the columns in the range on top of each other. So in this example, our combined data turns into a single column.


The result is:

Unpivot Data In Google Sheets Step 2

Step 3: Split The Data Into Columns

The final step is to split this new tall column into separate columns for each data type. You can see now why we needed to include the fox emoji so that we have a unique character to split the data on.

Wrap the formula from step 2 with the SPLIT function and set the delimiter to “”:


This splits the data into the tall data format we want. All that’s left is to add the correct column headings.

Unpivot Data In Google Sheets Step 3

Unpivot With Apps Script

You can also use Apps Script to unpivot data.

Have a look at the example sheet from the first answer of this Stack Overflow post.

Further Reading

For more information on the shape of datasets, have a read of Spreadsheet Thinking vs. Database Thinking.

IFS Function in Google Sheets

The IFS function in Google Sheets is used to test multiple conditions and outputs a value specified by the first test that evaluates to true.

It’s akin to a nested IF formula, although it’s not exactly the same. However, if you find yourself creating a nested IF formula then it’s probably easier to use this IFS function.

Example IFS Function in Google Sheets

Suppose you have a set of student exam scores and you want to assign different grades to the students:

Simple IFS Function In Google Sheets

In this scenario, you want to put students into three groups: i) those who score below 50 failed the exam, ii) those with scores between 50 and 79 passed the exam, and iii) students who scored 80 or above passed with distinction.

The IFS Function to do this is:

=IFS(B2<50,"Fail",B2<80,"Pass",B2>=80,"Pass with distinction")

The IFS function consists of pairs of arguments: a condition to test and a value.

If the conditional test is true, the value is displayed and the function stops. If not, and the test is false, the function tries the next test/value pair.

Consider row 2 of the example above, where Bob has a score of 70 in cell B2.

The first logical test and value pair is:


The function takes the value of 70 in cell B2 and compares it to the value of 50 to see if it’s less. This is false, so the “Fail” output is not displayed.

Instead, the IFS function in Google Sheets moves to the second logical test and value pair:


This time the logical test is 70 less than 80? evaluates to true, so the function displays “Pass”.

The IFS formula never reaches the third test/value pair.

The result of the IFS function in Google Sheets is an output that classifies the students:

IFS Function In Google Sheets Exam Grade

You could modify this IFS formula to assign grades “A”, “B”, “C” etc. instead, based on bands.

Note, it can also be done with a nested IF function. This gives the same result as the IFS function, but is more complex to understand:

=IF(B2<50,"Fail",IF(B2<80,"Pass","Pass with distinction"))

IFS Function in Google Sheets: Syntax

=IFS(condition1, value1, [condition2, value2, 因)

It takes a minimum of two arguments:


This is a logical test that evaluates to a TRUE or FALSE value.

For example A1 > 10 or ISBLANK(A1)


If condition1 is TRUE, then the IFS function will output this value.

condition2, value2

These are optional pairs of logical tests and values. If condition 1 is FALSE, the IFS moves on to test condition 2, then 3, then 4 etc.

IFS Function Notes

  • The conditions and values always come in a pair, so the IFS always has an even number of arugments.
  • The function reads the test/value pairs from left to right. It always starts with the leftmost argument as the first logical test.
  • There is no default value to display should all the conditions fail. The IFS function will then output an #N/A! error.
  • However, you can create a default fallback value by adding a penultimate argument TRUE (the logical test) and some value (the fallback value). This is illustrated in the bank account example below.

IFS function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

IFS Function Account Balance Example

Suppose you have a list of corporate bank balances and a table of interest rates, and you want to add the correct rate against the account balance so you can calculate the interest.

Account IFS Function Example

One way to do this is to use this IFS function in cell B2:


The first test compares the value in cell A2 to see if it’s less than $50,001, which means the account balance is in the $0 – $50,000 tier, so the formula returns a value of 0.5%.

If this first test fails, it tries the second logical test. If that fails, it tries the third logical test, etc.

As it’s written above, the interest rates are hardcoded in the formula, which is generally bad practice (because it’s hard to make changes and easy to make mistakes).

A better solution is to link the output values to the interest rate table, as shown in this version of the formula:


(Exercise for readers: take this a step further and link the bounds (50001, 100001 etc.) to the interest rate table instead of hard coding them.)

The output looks like this:

Account IFS Function Solution

Note: The final logical test can be replaced with the word TRUE, as the catch-all when none of the other conditions are met:


Alternative Solution

Note that this account example can also be solved with the VLOOKUP function using a TRUE value as the final argument.

This account example is covered in Day 6 of my free Advanced Formulas 30 Day Challenge course.

Advanced IFS Function in Google Sheets

This is somewhat contrived but will give you an idea of what’s possible with the IFS function.

In this scenario, imagine that two parties of buyers have viewed some properties.

The agent wants to record each party’s preferences. He remembers:

“Party 1 liked apartments under $200k”

“Party 2 liked houses or townhouses”

There are various ways you could solve this, including simply recording the preferences manually in a Sheet, but let’s see an IFS formula that does it automatically.

Here’s the data table:

Advanced IFS Formula Data

And here’s the formula in cell D2 that can be dragged down the column and across the row to fill in the peferences:

=IFS(ISBLANK($C3),"",AND(D$1="Party 1",$B3="Apartment",$C3<200000),"Yes", AND(D$1="Party 2", OR($B3="House",$B3="Townhouse")), "Yes",TRUE,"Not interested")

The test/value pairs are grouped as follows:

Test # Test In Formula Output
1 Is cell C3 blank? ISBLANK($C3) ""
2 Is it party 1 and Apartment and less than $200k? AND(D$1 = "Party 1",$B3 = "Apartment",$C3 < 200000) Yes
3 Is it party 2 and house or townhouse? AND(D$1 = "Party 2",OR($B3 = "House",$B3 = "Townhouse")) Yes
4 Catch all when conditions 1 – 3 not true TRUE Not interested

You’ll notice that test/value pairs 2 and 3 show how to use a nested AND function and a nested OR function to combine conditions.

The output of this formula is:

Advanced IFS Formula

SPLIT Function in Google Sheets

The SPLIT function in Google Sheets is used to divide a text string (or value) around a given delimiter, and output the separate pieces into their own cells.

SPLIT Function Examples

Let’s see a simple example using SPLIT to separate a list of names in cell A1:

Split Function Name Example

This simple SPLIT formula will separate these names, using the comma as the separator:


The result is 5 cells, each containing a name. Note that one cell looks blank because the text string in cell A1 has two adjacent commas with a space between them. The “space” is interpreted in the same way as the names and contained in the output:

Split Function Name Example

Now watch what happens if we include a space in the delimiter, i.e. ", "

=SPLIT(A1,", ")

The function splits on the comma "," and on the space " ", so the name “Mary Jo” split in two:

Undesirable behavior with SPLIT formula

This is probably not the desired behavior.

The third argument is an optional TRUE or FALSE that determines whether SPLIT considers each individual character of the delimiter (TRUE) or only the full combination as the separator to use (FALSE).

In our example, adding FALSE ensures that it only considers the combined comma/space string as the delimiter:

=SPLIT(A1,", ", FALSE)

And the output looks like this:

Split names in Google Sheets

There is a fourth argument too, which is optional and takes a TRUE/FALS value. It determines whether to remove blank cells or not in the output.

To illustrate this, consider this arrangement of data separated by semi-colons. Note the presence of two adjacent semi-colons with no data between them:

Split Formula Fourth Argument

The fourth argument determines whether to show or hide the blank cell caused by the two adjacent semi-colons.

To keep the blank cells, add FALSE as the fourth argument:


SPLIT Function in Google Sheets: Syntax

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

It takes 4 arguments:


This is the text string or value in the cell that you want to split. It can also be a reference to a cell with a value in, or even the output of a nested formula, provided that output is a string or value and not an array.


The character or characters used to split the text. Note that by default, all characters are used in the division. So a delimiter of “the” will split a text string on “the”, “he”,”t”,”h”,”e” etc.

This behavior can be controlled by the next argument:


This argument is optional and takes a TRUE or FALSE value only. If omitted, it’s assumed to be TRUE.

The TRUE behavior splits by individual characters in the delimiter and any combination of them. The FALSE behavior does not consider the characters separately, and only divides on the entire delimiter.


The fourth and final argument is optional and takes a TRUE or FALSE value only. If omitted, it’s assumed to be TRUE.

It specifies what to do with empty results in the SPLIT output. For example, suppose you’re splitting a text string with a "," and your string looks like this: “Ben,Bob,,Jenny,Anna”

Between the names Bob and Jenny are two commas with no value between them.

Setting this final argument of the SPLIT function to FALSE results in a blank cell in the output. If this fourth argument is omitted or set to TRUE, then the blank cell is removed and “Bob” and “Jenny” appear in adjacent cells.

SPLIT Function Notes

  • Delimiters in SPLIT are case sensitive. So “t” only splits on lower-case t’s in the text
  • The SPLIT function requires enough “space” for its output. If it splits a text string into 4 elements then it requires 4 cells (including the one the formula is in) on that row to expand into. If there is already data in any of these cells, it does NOT overwrite it but instead shows a #REF! error message
  • You can input a range as the first argument to the SPLIT function, but it requires an Array Formula wrapper to work
  • The output from the SPLIT function is an array of values that can be passed as the input into another formula, which may require the use of the Array Formula

Alternative Split Method

There’s an alternative way to split values in a Google Sheet.

Under the Data menu, there’s a feature called “Split text to columns” which will separate single columns into multiple columns, based on the delimiter you specify.

It’s a quick and easy way to split text.

Note that it overwrites existing data in your Sheet if the split columns overlap with any existing data.

Split function alternative

SPLIT Function Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

Advanced Examples of the SPLIT Formula in Google Sheets

Extract The N-th Word In A Sentence

You can wrap the SPLIT function output with an INDEX function to extract the word at a given position in a sentence. E.g. to extract the 4th word, use this formula:

=INDEX(SPLIT(A1," "),4)

Extract Nth Word in Google Sheets

If you combine this with a drop down menu using data validation, you can create a word extractor:

extract Nth Word Data Validation

Alphabetize Comma-Separated Strings With The SPLIT Function in Google Sheets

Suppose you have a list of words in a single cell that you want to sort alphabetically:

Formula Challenge 3

This formula will rearrange that list alphabetically:


It splits the string of words, applies the TRANSPOSE function to convert into a column so it can be sorted using the SORT function, and then recombines it with the JOIN function.

Formula Challenge 3 Solution

Read more in Formula Challenge #3: Alphabetize Comma-Separated Strings.

Splitting and Concatenating Strings

The SPLIT is useful in more advanced formulas as a way to divide an array into separate elements, do some work on those elements (e.g. sort them) before recombining them with another function, like the JOIN function.

For example, this formula will add surnames to a list of first names in a cell:

=ArrayFormula(TRIM(JOIN(", ",SPLIT(A2,", ")&" Smith")))

which looks like this in your Google Sheet:

Split Function In Google Sheets To Add Surnames

Using the onion framework to analyze this formula, starting from the innermost function and working out, it splits the text string, joins on the surname “Smith”, trims the excess trailing space and finally outputs an array by using the Array Formula.

Advanced SPLIT Array Techniques

The SPLIT function in Google Sheets is used in a number of the complex IMPORT formulas for retrieving social media statistics into your Google Sheet.

The SPLIT function was also used in this exceedingly wacky unpivot formula in Google Sheets (see Solution 4):

QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&""&Sheet1!B1:1&""&Sheet1!B2:Z&"", )), , 500000)), , 500000)),""))),""),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1",0)})

All in all, SPLIT is a useful function!

REPT Function in Google Sheets

The REPT function in Google Sheets is used to repeat an expression a set number of times.

The REPT formula to repeat “Go! ” three times is:

=REPT("Go! ", 3)

Notice the additional space added after the exclamation point, so that there is a space between the repeated values in the output.

More REPT Function Examples

Rept formula examples in Google Sheets

Column A contains the values you want to repeat.

Column B contains the number of repetitions.

The formula is then very straightforward:


As you can see, for each row the value in A is repeated per the number in column B and joined into one long string.

REPT Function in Google Sheets: Syntax

The REPT function takes two arguments and both are required.

=REPT(text_to_repeat, number_of_repetitions)


This is a text string or cell reference that you want to repeat.


This is a positive integer indicating how many times you want to repeat the input text.

Notes on using the REPT function

  • The return value (the output of the REPT function) is a string value in a single cell
  • Setting the number of repetitions to 0 results in a cell containing a blank string (not a true blank cell though. ISBLANK will still give FALSE)
  • Setting the number of repetitions to -1 results in a #VALUE! error
  • If you want spaces between the repeated text strings, you must add that as the final character of the input string (as shown in the "Go! " example above). This results in a final trailing space, which can be removed with the TRIM function if required
  • The number_of_repetitions cant exceed the character limit of a cell: 32,000 characters. If it does, you’ll see a #VALUE! error
  • Ranges can be repeated by wrapping the REPT function in an Array Formula

REPT function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

Using The REPT Function For Repeated Values In Separate Cells

What if you don’t want to create a long string of repeated values in a single cell, but instead want to output the repeated value across a range of cells, such that each value is in its own cell?

That’s possible with this formula, assuming the value is in cell A1 and the number of repetitions is in cell B1:


This particular formula outputs 10 values “text” in a column. (You can remove the transpose if you want the output across a row.)

The Queen symbol “” is added to the end of the repeated value to act as a unique value used by the SPLIT function to divide the repeated string.

REPT formula to output values in separate cell

Note: you can also achieve this affect with the SEQUENCE function:


This particular variation uses the SEQUENCE function to output an array 1,2,3,…10. The TEXT function then “tricks” all of these to become empty strings. We then concatenate the repeated value from cell A1 onto this string. Finally, the Array Formula ensures that the output is an array.

Repeated Images with REPT Formula in Google Sheets

This REPT formula will repeat the specified image across a row. It uses the same Queen symbol “” trick as the previous formula.


The output looks like this:

Repeated image using the REPT function in Google Sheets

Using REPT As Logical Formulas

The REPT function can be used instead of IF formulas in specific situations.

It’s quite a clever trick that works because TRUE is equivalent to the number 1, and FALSE to the number 0, in formulas.

So, by putting a logical test, which evaluates to TRUE or FALSE, equivalent 1 or 0, in the number of repetitions argument, we can either show 1 value or 0 values using REPT.

For example, this formula identifies values over $1,000:

=REPT("Over $1,000",A1 > 1000)

And the output looks like this:

Rept Function Logical Formula

This formula works because the logical test checks if the value in cell A1 is greater than $1,000. If the result is TRUE, it’s interpreted as a value of 1, which then repeats the text in the REPT formula once. So the output is “Over $1,000”.

If the value is less than or equal to $1,000, then the test evaluates to FALSE, equivalent to 0 input for the REPT formula, so the output is a blank string in the cell.

This technique can be taken a step further to check multiple conditions are TRUE.

For example, the following formula checks whether the value is over $1,000 AND comes from Client A:

=REPT("Over $1,000 and Client A",(B1>1000)*(A1="Client A"))

The two logical tests evaluate to TRUE or FALSE, which are interpreted as 1 or 0 by the formula.

When the value is greater than $1,000 and from Client A, it’s equivalent to:

TRUE * TRUE = 1 * 1 = 1

In which case, the formula outputs the string “Over $1,000 and Client A”

Multiplication using “*” as shown above is for the AND case, where both conditions are true.

To do the OR case, you use addition “+”. If either value is TRUE and the other FALSE, it’s equivalent to 1 + 0 = 1. When both are true it’s equivalent to 1 + 1 = 2, so the REPT would repeat the value twice which we don’t want.

To fix this, wrap it with a MIN formula to set to 1 if the value is above 1.

=REPT("Over $1,000 and Client A",MIN((B1>1000)+(A1="Client A"),1)

In Cell Charts with REPT Function in Google Sheets

Bar chart with REPT Function

In cell bar chart With Rept Function

The formula to do this is:


You can use any character as the repeated symbol, for example you could use tacos with CHAR(127790):

tacos in Google Sheets

Vertical Bar Chart with REPT function

You can easily extend the technique above to display the bar chart in a single cell, with a vertical orientation, using this formula:


which looks like this (using dots as the repeated symbol):

Rept Column Chart

Dynamic Width Bar Charts Using REPT function

This is an interesting, although not particularly useful, formula which uses the CELL function to access the width of a cell and then sets the REPT to repeat the value to match the width of the cell:

Dynamic Width Bar Chart With Rept

The formula to do this is:


It uses the random number generator and the spreadsheet calculation setting to be “On change and every minute” to force the CELL function to re-calculate the width value each time there is a change.

The 1.25 factor at the end is simply to adjust the largest bar to fit the full width. It depends on the character you use to repeat and how wide it is. Feel free to experiment.

Padding Strings With The REPT Function

REPT can also be used to pad text strings.

With a value in A1, this formula adds a variable number of underscores to the end of the string:


If you want to have the text strings to have equal widths you’ll also need to use a font where the letters are equally spaced, like SOURCE CODE PRO.

This approach can also be achieved with numbers, although it’s much better to use custom number formatting, because that preserves the value as type number instead of converting to a string.

PI Function in Google Sheets And Other Fun Facts

The PI function in Google Sheets returns the value of the mathematical constant pi () to 9 decimal places: 3.141592654

Pi is defined as the ratio of a circle’s circumference to its diameter.

Pi is denoted by the Greek lowercase letter

PI Function in Google Sheets: Syntax


There are no arguments. If you put anything between the brackets the formula will give you an error.

Circle Calculations With The PI Function

If you know the circumference of a circle then you can calculate the diameter and vice versa.

How to calculate the circumference

Suppose you have a diameter of 10 (the units are irrelevant).

Then you calculate the circumference with this formula: the diameter multiplied by pi.

= 10 * PI()

How to calculate the diameter

Now suppose you know the circumference is value 10.

The diameter is calculated as the circumference divided by pi:

= 10 / PI()

How to calculate the area of a circle

The formula for the area of a circle is * r簡 (pi times the radius squared).

To calculate the radius, you must divide the diameter by 2.

So, knowing the diameter d, you can calculate the area of a circle as follows: * (d/2)簡

Taking a diameter of 10, the area is:

= PI() * ( 10 / 2 )^2

for an answer 78.5398

Creating the PI Symbol with the CHAR Function

The amazing CHAR function, which converts numbers into characters per the Unicode table, can output the symbol for pi in your Google Sheet.

Use this formula to output the pi symbol :


Setting the cell to the Merriweather font gives it the nice rounded appearance shown in this image:

Pi Symbol With Char Function

By the way, this formula:


will output an image of an entirely different type of pie in your Google Sheet:

Pie in a Google Sheet

PI function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

Approximating Pi in Google Sheets With the Nilakantha Series

The Nilakantha series, named after the 15th century mathematician of the same name, is an infinite series that converges on pi as the number of iterations rises. It converges quickly so it’s a good algorithm to use to compute the digits of pi.

The Nilakantha series is:

Nilakantha Series
Image source: Wikipedia

Let’s build an approximation of pi in a Google Sheet using this series.

Nilakantha Series in Google Sheets With The PI Function

In cell A1, enter this SEQUENCE formula:


And in B1:


Then in C1:


These three formulas give us three columns of numbers with 1,000 digits each. They start with 2, 3 and 4 respectively and increment by 2.

Look closely at the infinite series definition above and you’ll notice that each row represents a denominator for each iteration of the series.

In cell D1, multiply the values together for each row:


Copy this formula down the column.

This formula generates the denominators for the series.

In cell E1, put this product as the denominator to calculate the series values:

= 4 / D1

Next, we need to alternate adding and subtracting these values, which we can do with this formula:

=IF( ISODD( ROW() ), E1 , UMINUS( E1 ) )

This IF formula checks if we’re on an odd numbered row (e.g. row 1) using the ISODD function and, if we are, returns the number unchanged. But if we’re on an even numbered row, it returns the negative value from column E, using the UMINUS function.

Finally we can approximate pi in column G.

In G1, put this formula to start the series:

= 3 + F1

In cell G2, put this formula:

= G1 + F2

and drag this formula all the way to the bottom of the range.

With 1,000 rows, we get a value for pi of 3.1415926533, which is a similar level of accuracy to the PI function.

Here are the first 16 iterations, the remaining rows up to 1,000 can be found in the template linked above in this article:

Nilakantha Series to approximate pi in Google Sheets

Now, you might be wondering how many digits of pi have been calculated…

…well it’s a whopping 31.4 trillion digits!

Obviously, that is not a calculation you can perform in your Google Sheet, but interestingly, it was performed on Google Cloud infrastructure!

Update: The record is now 50 trillion digits, broken by an enthusiast with a self-built server setup. Super impressive!

Read more about the chronology of computing digits of pi.

Getting More Digits of Pi in Google Sheets

Whilst we can’t get 50 trillion digits of pi into a Google Sheet, we can call the Pi Delivery API and retrieve more digits.

Here’s the code to request the first 1,000 digits of pi and display them in a Google Sheet as a text string (because numbers get truncated):

 * function to call the Pi API
 * https://pi.delivery/#apipi_get
function getPI() {
  // endpoint
  const root = 'https://api.pi.delivery/v1/pi';
  // start point and number of digits
  const startDigit = 0;
  const numDigits = 1000; // max request is 1000 digits in 1 call

  // create endpoint
  const endpoint = root + '?start=' + startDigit + '&numberOfDigits=' + numDigits;

  // call the PI API to fetch digits of pi
  const response = UrlFetchApp.fetch(endpoint);
  const result = JSON.parse(response.getContentText());
  // get pi
  // keep pi as a string otherwise it gets truncated if a number
  // add single tick in front of the 3 to avoid automatic conversion to number in Sheet
  const piDigits = result.content;
  const pi = "'3." + piDigits.substring(1); 

  // paste result into Google Sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const piSheet = ss.getSheetByName('PI API');

The API is limited to calls of up to 1,000 digits at a time, so if you want more than that you’ll need to expand the script above to request batches of 1,000.

More Resources For Mathematics in Google Sheets

Complex Numbers In Google Sheets

How To Draw The MandelBrot Set In Google Sheets, Using Only Formulas