Apps Script Sheet Pie chart image

Creating a Google Apps Script to generate a pie chart image from Google Sheets data involves several steps. First, you need to have a Google Sheet with sample data, then write a script to process this data and generate a pie chart, and finally, export this chart as an image. Here’s a guide to accomplish this:

Step 1: Prepare Sample Data in Google Sheets

• Open Google Sheets and create a new sheet.
• Enter Sample Data: For instance, you can have two columns: Category and Values.
  Example:

| Category | Values |

|———-|——–|

| Food | 300 |

| Rent | 700 |

| Transport| 150 |

| Utilities| 100 |

Step 2: Write the Google Apps Script

• Open Script Editor: In Google Sheets, go to Extensions > Apps Script.
• Write the Script: Copy and paste the following script into the script editor.

function generatePieChart() {

 const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

 const range = sheet.getDataRange();

 const chart = sheet.newChart()

 .setChartType(Charts.ChartType.PIE)

 .addRange(range)

 .setPosition(5, 5, 0, 0)

 .build();

 const chartBlob = chart.getBlob();

 const chartUrl = DriveApp.createFile(chartBlob).getDownloadUrl();

 Logger.log(“Chart URL: ” + chartUrl);

}

• Save the Script: Give your project a name and save the script.

Step 3: Run the Script and Generate the Pie Chart

• Run the Script: Click on the play/run button in the script editor to execute the generatePieChart function.
• Authorize Access: The first time you run the script, you’ll need to authorize the script to access your Google Sheets and Drive data.
• Check the Logs: After running the script, check the logs (View > Logs) for the URL of the generated pie chart image.

Notes:

• The script creates a pie chart based on the data in your active sheet and then saves it as an image file in your Google Drive.
• The generated URL in the logs is a direct link to the image file in your Google Drive.
• Make sure your Google Drive is not full, as the script needs space to upload the chart image.
• You can customize the script to fit specific data ranges or chart types based on your needs.

This basic script serves as a starting point. Depending on your specific requirements, you might need to modify the script, for instance, to format the chart, handle different data structures, or integrate with other Google services.

Table data in Sheets

Here’s the sample data presented in a table format. This data can be used in your Google Sheets to generate the pie chart with the Google Apps Script:

Category	Values
Food	300
Rent	700
Transport	150
Utilities	100

You can input this data into your Google Sheet as follows:

1. Open a new or existing Google Sheet.
2. In the first column (A), input the categories: “Food”, “Rent”, “Transport”, and “Utilities” in cells A2 through A5.
3. In the adjacent column (B), input the corresponding values: 300, 700, 150, and 100 in cells B2 through B5.

Here’s how it should look in your Google Sheet:

A	B
Food	300
Rent	700
Transport	150
Utilities	100

Once you have this data in your sheet, you can use the script provided earlier to generate a pie chart based on this data. Remember to adjust the data range in the script if your data is located in different cells.