Editing article

Title

Summary

Content

<figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/0*2828W-RsKQh10X2F.png" /></figure><h3>Abstract</h3>A new large language model (LLM) called Gemini with an API is now available, allowing developers to analyze vast amounts of data. This report explores trends in Google Apps Script by using the Gemini 1.5 API to analyze questions on Stack Overflow.<h3>Introduction</h3>The release of the LLM model Gemini as an API on Vertex AI and Google AI Studio has opened a world of possibilities. <a href="https://deepmind.google/technologies/gemini/#introduction">Ref</a> The Gemini API significantly expands the potential of various scripting languages, paving the way for diverse applications. Additionally, Gemini 1.5 has recently been released in AI Studio. <a href="https://blog.google/technology/ai/google-gemini-next-generation-model-february-2024/#sundar-note">Ref</a> We can expect the Gemini 1.5 API to follow suit soon.The difference between Gemini 1.0 and Gemini 1.5 is as following table created by Gemini.<iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9bfb36b70efe743ac9169ae744e6fe3e/href">https://medium.com/media/9bfb36b70efe743ac9169ae744e6fe3e/href</a></iframe>Given Gemini 1.5’s ability to analyze large data, this report leverages the Gemini 1.5 API to analyze trends in Google Apps Script from the data of questions on Stackoverflow. We achieve this by examining all questions tagged google-apps-script from 2008 to 2024 (only 2024, from January to March 28) on Stack Overflow.<h3>Step</h3>The steps of this analysis are as follows.<h3>1. Retrieve questions from Stackoverflow</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/889/0*PENJvQbml73daj2L.png" /></figure>As the base data of Stackoverflow, I used the data retrieved at “<a href="https://medium.com/google-cloud/trend-of-google-apps-script-tag-on-stackoverflow-2024-584e20fb892c">Trend of google-apps-script Tag on Stackoverflow 2024</a>”. In that report, all questions including a tag google-apps-script from 2008 to 2024 have already been retrieved. The data from January 1, 2024, to March 28, 2024, was retrieved in this report. All data is retrieved by StackExchange API. <a href="https://api.stackexchange.com/docs">Ref</a> This flow can be seen in the above image. As a result, 17 CSV files including the data from Stackoverflow were created. The total number of questions including google-apps-script tag from 2008 to March 28, 2024, on Stackoverflow was 54,135.<h3>2. Generate texts for each year</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ldN8YDuXzi6_uqmS.png" /></figure>As the next step, each summary is created from the retrieved 17 CSV files using Gemini 1.5 API. Because in the current stage, all 17 CSV data cannot be directly used by one API call. So, I separated them. This flow can be seen in the above image. By this flow, a text file including the summaries of each year was created.<h3>3. Generate text from all year</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ZSv82VONRiP7AFS8.png" /></figure>As the next step, a summary is created from a text file including summaries from 2008 to 2024. This flow can be seen in the above image. By this flow, the summary of all data from 2008 to 2024 is created.<h3>Usage</h3>In order to test this script, please do the following steps.<h3>1. Create an API key</h3>Please access <a href="https://makersuite.google.com/app/apikey">https://makersuite.google.com/app/apikey</a> and create your API key. At that time, please enable Generative Language API at the API console. This API key is used for this sample script.This official document can be also seen. <a href="https://ai.google.dev/">Ref</a>.<h3>2. Create a Google Apps Script project</h3>In this report, Google Apps Script is used. Of course, the method introducing this report can be also used in other languages.Please create a standalone Google Apps Script project. Of course, this script can be also used with the container-bound script.And, please open the script editor of the Google Apps Script project.<h3>3. Base data</h3>Here, prepare the data as described in the section “1. Retrieve questions from Stack Overflow.” In this report, we focus on the values of “creation_date,” “title,” “score,” and “tags.” While I initially wanted to include the body of the question, it significantly increased the number of tokens for processing. Therefore, the body was excluded. However, I believe the trend can still be obtained from the remaining values: “creation_date,” “title,” “score,” and “tags.”<h3>4. Base script</h3>The base script for processing this analysis is as follows. Please copy and paste the following script to the script editor of Google Apps Script and save the script.In this script, the model models/gemini-1.5-pro-latest was used for generating texts.<pre>/** * Sample script for this report. */ class DoGemini { /** * @param {Object} object Object using this library. */ constructor(object = {}) { const model = &quot;models/gemini-1.5-pro-latest&quot;; this.baseUrl = `https://generativelanguage.googleapis.com/v1beta/${model}`; this.apiKey = object.apiKey || null; this.headers = object.apiKey ? null : { authorization: `Bearer ${object.token || ScriptApp.getOAuthToken()}`, }; this.retry = 5; this.folderId = object.folderId || &quot;root&quot;; this.object = object; } /** * ### Description * Create summaries for each year of the data. * * @returns {String} A file ID of the file including the created summaries. */ createSummariesOfEachYear() { const headerText = [&quot;creation_date&quot;, &quot;title&quot;, &quot;score&quot;, &quot;tags&quot;].join(&quot;,&quot;); const files = DriveApp.getFolderById(this.folderId).getFilesByType( MimeType.CSV ); const fileList = []; while (files.hasNext()) { const file = files.next(); const csv = file.getBlob().getDataAsString().trim() || &quot;No data.&quot;; const filename = file.getName(); fileList.push({ filename, csv }); } if (fileList.length == 0) { throw new Error(&quot;No CSV files.&quot;); } fileList.sort((a, b) =&gt; (a.filename &gt; b.filename ? 1 : -1)); console.log( `--- File list\n${JSON.stringify( fileList.map(({ filename }) =&gt; filename), null, 2 )}` ); const res = fileList.map(({ filename, csv }) =&gt; { console.log(`Now processing: ${filename}`); const q = [ `Summary the trend from the following CSV data. The CSV data is questions related to Google Apps Script in Stackoverflow. Namely, summarize the trend of questions on Stackoverflow from CSV data.`, `Consider the creation date of the first column.`, `Consider the affection of the advent of AI.`, `Consider the history of Google Apps Script.`, `Include a value, that you evaluated the activity level of Google Apps Script between 1 and 100, in the response without overestimating.`, ``, `${filename}`, `The format of CSV data is as follows.`, ``, `[Format of CSV data]`, `Created date of the question, Title of the question, Score of the question, Tags related to the question`, ``, `[CSV data]`, `${headerText}`, `${csv}`, ].join(&quot;\n&quot;); const summary = this.generateContent_(q); return { filename, ...summary }; }); return DriveApp.getFolderById(this.folderId) .createFile(&quot;summariesOfEachYear.txt&quot;, JSON.stringify(res)) .getId(); } /** * ### Description * Create a summary of all years. * * @param {String} fileId File ID including summaries of each year. * @returns {String} A file ID of the file including the created summary. */ createSummaryOfAllYears(fileId) { if (!fileId) { throw new Error( &quot;Please set the file ID of the file including summaries of each year.&quot; ); } const headerText = [&quot;year&quot;, &quot;summary&quot;].join(&quot;,&quot;); const data = JSON.parse( DriveApp.getFileById(fileId).getBlob().getDataAsString() ); const csv = data.map((e) =&gt; `${e.filename},${e.response}`).join(&quot;\n&quot;); // const csv = data.map(e =&gt; `${e.filename},&#39;${JSON.stringify(e.response).replace(/&#39;/, &quot;\&#39;&quot;)}&#39;`).join(&quot;\n&quot;); const q = [ `Summary the trend from the following CSV data. The CSV data is the summary of trend of questions related to Google Apps Script in Stackoverflow every year. The summaries were created by Gemini API.`, `Consider the each year of the first column.`, `Consider the affection of the advent of AI.`, `Consider the history of Google Apps Script.`, `At the last of summary, add all values, that you evaluated the activity level of Google Apps Script between 1 and 100, in the response without overestimating. Output the values as an array. The array format is [[&quot;year&quot;, &quot;activity value&quot;],[&quot;year&quot;, &quot;activity value&quot;],,,].`, ``, `The format of CSV data is as follows.`, ``, `[Format of CSV data]`, `year, Summary of trend of questions`, ``, `[CSV data]`, `${headerText}`, `${csv}`, ].join(&quot;\n&quot;); const { response } = this.generateContent_(q); return DriveApp.getFolderById(this.folderId) .createFile(&quot;summaryOfAllYears.txt&quot;, response) .getId(); } /** * ### Description * Count tokens of inputted values with Gemini API. * * @param {Object} options Object for UrlFetchApp. * @returns {Object} totalTokens */ countToken_(options) { const url = `${this.baseUrl}:countTokens` + (this.apiKey ? `?key=${this.apiKey}` : &quot;&quot;); const res = this.fetch_({ url, ...options }); return JSON.parse(res.getContentText()); } /** * ### Description * Generate content with Gemini API. * * @param {String} q Text for prompt. * @returns {UrlFetchApp.HTTPResponse|String[]} Response from API. When pageToken is used, String[] is returned. */ generateContent_(q) { const payload = { contents: [{ parts: [{ text: q }], role: &quot;user&quot; }] }; const options = { payload: JSON.stringify(payload), contentType: &quot;application/json&quot;, muteHttpExceptions: true, }; if (this.headers) { options.headers = headers; } const totalTokens = this.countToken_(options); console.log(totalTokens); // Confirm the total tokens in the log. const url = `${this.baseUrl}:generateContent` + (this.apiKey ? `?key=${this.apiKey}` : &quot;&quot;); const res = this.fetch_({ url, ...options }); const obj = JSON.parse(res.getContentText()); if ( obj.candidates &amp;&amp; obj.candidates.length &gt; 0 &amp;&amp; obj.candidates[0].content.parts.length &gt; 0 ) { return { totalTokens, response: obj.candidates[0].content.parts[0].text }; } else { this.retry--; console.warn(&quot;No response. Retry again.&quot;); if (this.retry &gt; 0) { this.generateContent_(q); } else { console.error(&quot;No response.&quot;); } } } /** * ### Description * Request Gemini API. * * @param {Object} obj Object for using UrlFetchApp.fetchAll. * @returns {UrlFetchApp.HTTPResponse} Response from API. */ fetch_(obj) { obj.muteHttpExceptions = true; const res = UrlFetchApp.fetchAll([obj])[0]; if (res.getResponseCode() != 200) { throw new Error(res.getContentText()); } return res; } }</pre><h3>5. Script for “Generate texts for each year”</h3>This script creates a text file including summaries from 2008 to 2024. The file is created in the same folder as the CSV files. Of course, you can also see each summary from the file.<pre>function process1() { const apiKey = &quot;###&quot;; // Please set your API key. const object = { apiKey, folderId: &quot;###&quot;, // Please set the folder ID of the folder including 17 CSV files including data from Stackoverflow. }; const dg = new DoGemini(object); const res = dg.createSummariesOfEachYear(); console.log(res); }</pre>When this script is run, the number of tokens for each year can be seen at the following chart.<figure><img alt="" src="https://cdn-images-1.medium.com/max/981/0*bSWLQd16SvydbZbD.png" /></figure>This chart reveals that the number of tokens exceeds 32,000 in most years. In this context, the Gemini 1.5 API has proven to be a valuable tool.<h3>6. Script for “Generate text from all year”</h3>This script creates a text file of a summary of the trend of all questions including a tag google-apps-script posted from 2008 to 2024. In this case, the result file is created in the root folder.<pre>function process2() { const apiKey = &quot;###&quot;; // Please set your API key. const fileId = &quot;###&quot;; // Please set the file ID of a text file including summaries of each year. const object = { apiKey }; const dg = new DoGemini(object); const res = dg.createSummaryOfAllYears(fileId); console.log(res); }</pre><h3>Result</h3>When the above scripts are run, the following result is obtained.<pre>## Summary of Google Apps Script Activity on Stack Overflow: Based on the provided summaries of Google Apps Script questions on Stack Overflow from 2008 to 2024, we can observe the following trends: **Early Stage (2008-2009):** * Minimal activity as the platform was in its early stages of development and adoption. **Growth and Adoption (2010 onwards):** * Gradual increase in questions, indicating growing interest and adoption. * Focus on basic functionalities initially, followed by more complex and diverse topics as the platform matured. * Active community participation and knowledge sharing. **Impact of AI:** * While not explicitly evident in the earlier data, the potential for AI integration with Google Apps Script is recognized and may influence future trends. **Activity Level:** * Estimated activity level between **60 and 70 out of 100**, indicating a healthy and active community with room for further growth. **Specific Observations:** * Common topics include: * Basic syntax and functionality * Integration with Google Sheets, Forms, and other Google products * Email automation * Data manipulation * Triggering scripts * Web app development * Recurring challenges faced by users: * Authorization and permission errors * Understanding specific methods and syntax * Debugging scripts * Optimizing script performance **Overall, the data suggests a steady and ongoing interest in Google Apps Script, with a growing user base and increasingly complex use cases. While AI integration is not yet a dominant theme, it has the potential to influence future trends and applications.** **Estimated Activity Levels:** Based on the analysis of each year&#39;s data, here&#39;s an array summarizing the estimated activity levels of Google Apps Script on Stack Overflow: ``` [ [&quot;2008-2009&quot;, 10], // Estimated low activity due to early stage [&quot;2010-2011&quot;, 30], // Moderate activity during early growth [&quot;2011-2012&quot;, 60], // Increasing activity and diversification of topics [&quot;2012-2013&quot;, 60], // Steady growth and expanding capabilities [&quot;2013-2014&quot;, 60], // Moderately active and engaged community [&quot;2014-2015&quot;, 65], // Consistent activity with focus on both basic and advanced functionalities [&quot;2015-2016&quot;, 75], // Healthy and active community, diverse topics covered [&quot;2016-2017&quot;, 70], // Consistent activity, focus on integrations and web app development [&quot;2017-2018&quot;, 70], // Steady interest, diverse use cases, and demand for advanced functionalities [&quot;2018-2019&quot;, 65], // Consistent activity, potential for future AI integration [&quot;2019-2020&quot;, 65], // Sustained interest, potential influence of AI in automation [&quot;2020-2021&quot;, 65], // Consistent activity, continued relevance and adoption [&quot;2021-2022&quot;, 75], // Vibrant and active community, growing adoption and complex use cases [&quot;2022-2023&quot;, 75], // Consistent activity, potential for AI integration in specific areas [&quot;2023-2024&quot;, 65], // Steady interest, focus on basic functionalities and integrations ] ``` **Note:** These activity levels are estimations based on the provided data and general knowledge of Google Apps Script&#39;s adoption and development. A more precise assessment would require access to a larger dataset and more detailed information about question topics and user engagement.</pre>When the activity level is evaluated by Gemini 1.5 API for each year is exported as a chart, it becomes as follows.<figure><img alt="" src="https://cdn-images-1.medium.com/max/981/0*Y_vIz1H0Xoz6yVBb.png" /></figure>The above results confirm various insights about the trend of Google Apps Script on Stack Overflow. Additionally, the Gemini 1.5 API emerged as a valuable tool for data analysis.<h3>Note</h3><ul><li>The top illustration was created by <a href="https://gemini.google.com/">Gemini</a> with giving the abstract.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c6b448e36461" width="1" height="1" alt=""><hr><a href="https://medium.com/google-cloud/analyzing-trends-of-google-apps-script-from-questions-on-stackoverflow-using-gemini-1-5-api-c6b448e36461">Analyzing Trends of Google Apps Script from Questions on Stackoverflow using Gemini 1.5 API</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

Author

Link

Published date

Image url

Feed url

Guid

Hidden blurb

--- !ruby/object:Feedjira::Parser::RSSEntry
title: Analyzing Trends of Google Apps Script from Questions on Stackoverflow using
 Gemini 1.5 API
url: https://medium.com/google-cloud/analyzing-trends-of-google-apps-script-from-questions-on-stackoverflow-using-gemini-1-5-api-c6b448e36461?source=rss----e52cf94d98af---4
author: Kanshi Tanaike
categories:
- google-cloud-platform
- generative-ai
- machine-learning
- google-apps-script
- gemini
published: 2024-03-31 02:51:15.000000000 Z
entry_id: !ruby/object:Feedjira::Parser::GloballyUniqueIdentifier
 is_perma_link: 'false'
 guid: https://medium.com/p/c6b448e36461
carlessian_info:
 news_filer_version: 2
 newspaper: Google Cloud - Medium
 macro_region: Blogs
rss_fields:
- title
- url
- author
- categories
- published
- entry_id
- content
content: '<figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/0*2828W-RsKQh10X2F.png"
 /></figure><h3>Abstract</h3>A new large language model (LLM) called Gemini with
 an API is now available, allowing developers to analyze vast amounts of data. This
 report explores trends in Google Apps Script by using the Gemini 1.5 API to analyze
 questions on Stack Overflow.<h3>Introduction</h3>The release of the LLM model
 Gemini as an API on Vertex AI and Google AI Studio has opened a world of possibilities.
 <a href="https://deepmind.google/technologies/gemini/#introduction">Ref</a> The
 Gemini API significantly expands the potential of various scripting languages, paving
 the way for diverse applications. Additionally, Gemini 1.5 has recently been released
 in AI Studio. <a href="https://blog.google/technology/ai/google-gemini-next-generation-model-february-2024/#sundar-note">Ref</a>
 We can expect the Gemini 1.5 API to follow suit soon.The difference between
 Gemini 1.0 and Gemini 1.5 is as following table created by Gemini.<iframe src=""
 width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9bfb36b70efe743ac9169ae744e6fe3e/href">https://medium.com/media/9bfb36b70efe743ac9169ae744e6fe3e/href</a></iframe>Given
 Gemini 1.5’s ability to analyze large data, this report leverages the Gemini 1.5
 API to analyze trends in Google Apps Script from the data of questions on Stackoverflow.
 We achieve this by examining all questions tagged google-apps-script from 2008 to
 2024 (only 2024, from January to March 28) on Stack Overflow.<h3>Step</h3>The
 steps of this analysis are as follows.<h3>1. Retrieve questions from Stackoverflow</h3><figure><img
 alt="" src="https://cdn-images-1.medium.com/max/889/0*PENJvQbml73daj2L.png" /></figure>As
 the base data of Stackoverflow, I used the data retrieved at “<a href="https://medium.com/google-cloud/trend-of-google-apps-script-tag-on-stackoverflow-2024-584e20fb892c">Trend
 of google-apps-script Tag on Stackoverflow 2024</a>”. In that report, all questions
 including a tag google-apps-script from 2008 to 2024 have already been retrieved.
 The data from January 1, 2024, to March 28, 2024, was retrieved in this report.
 All data is retrieved by StackExchange API. <a href="https://api.stackexchange.com/docs">Ref</a>
 This flow can be seen in the above image. As a result, 17 CSV files including the
 data from Stackoverflow were created. The total number of questions including google-apps-script
 tag from 2008 to March 28, 2024, on Stackoverflow was 54,135.<h3>2. Generate
 texts for each year</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ldN8YDuXzi6_uqmS.png"
 /></figure>As the next step, each summary is created from the retrieved 17 CSV
 files using Gemini 1.5 API. Because in the current stage, all 17 CSV data cannot
 be directly used by one API call. So, I separated them. This flow can be seen in
 the above image. By this flow, a text file including the summaries of each year
 was created.<h3>3. Generate text from all year</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ZSv82VONRiP7AFS8.png"
 /></figure>As the next step, a summary is created from a text file including
 summaries from 2008 to 2024. This flow can be seen in the above image. By this flow,
 the summary of all data from 2008 to 2024 is created.<h3>Usage</h3>In order
 to test this script, please do the following steps.<h3>1. Create an API key</h3>Please
 access <a href="https://makersuite.google.com/app/apikey">https://makersuite.google.com/app/apikey</a>
 and create your API key. At that time, please enable Generative Language API at
 the API console. This API key is used for this sample script.This official
 document can be also seen. <a href="https://ai.google.dev/">Ref</a>.<h3>2. Create
 a Google Apps Script project</h3>In this report, Google Apps Script is used.
 Of course, the method introducing this report can be also used in other languages.Please
 create a standalone Google Apps Script project. Of course, this script can be also
 used with the container-bound script.And, please open the script editor of
 the Google Apps Script project.<h3>3. Base data</h3>Here, prepare the data
 as described in the section “1. Retrieve questions from Stack Overflow.” In this
 report, we focus on the values of “creation_date,” “title,” “score,” and “tags.”
 While I initially wanted to include the body of the question, it significantly increased
 the number of tokens for processing. Therefore, the body was excluded. However,
 I believe the trend can still be obtained from the remaining values: “creation_date,”
 “title,” “score,” and “tags.”<h3>4. Base script</h3>The base script for processing
 this analysis is as follows. Please copy and paste the following script to the script
 editor of Google Apps Script and save the script.In this script, the model
 models/gemini-1.5-pro-latest was used for generating texts.<pre>/** * Sample
 script for this report. */ class DoGemini { /** * @param {Object}
 object Object using this library. */ constructor(object = {}) { const
 model = &quot;models/gemini-1.5-pro-latest&quot;; this.baseUrl = `https://generativelanguage.googleapis.com/v1beta/${model}`; this.apiKey
 = object.apiKey || null; this.headers = object.apiKey ? null :
 { authorization: `Bearer ${object.token || ScriptApp.getOAuthToken()}`, }; this.retry
 = 5; this.folderId = object.folderId || &quot;root&quot;; this.object
 = object; } /** * ### Description * Create summaries for
 each year of the data. * * @returns {String} A file ID of the file including
 the created summaries. */ createSummariesOfEachYear() { const
 headerText = [&quot;creation_date&quot;, &quot;title&quot;, &quot;score&quot;, &quot;tags&quot;].join(&quot;,&quot;); const
 files = DriveApp.getFolderById(this.folderId).getFilesByType( MimeType.CSV ); const
 fileList = []; while (files.hasNext()) { const file = files.next(); const
 csv = file.getBlob().getDataAsString().trim() || &quot;No data.&quot;; const
 filename = file.getName(); fileList.push({ filename, csv }); } if
 (fileList.length == 0) { throw new Error(&quot;No CSV files.&quot;); } fileList.sort((a,
 b) =&gt; (a.filename &gt; b.filename ? 1 : -1)); console.log( `---
 File list\n${JSON.stringify( fileList.map(({ filename }) =&gt; filename), null, 2 )}` ); const
 res = fileList.map(({ filename, csv }) =&gt; { console.log(`Now processing:
 ${filename}`); const q = [ `Summary the trend from the following
 CSV data. The CSV data is questions related to Google Apps Script in Stackoverflow.
 Namely, summarize the trend of questions on Stackoverflow from CSV data.`, `Consider
 the creation date of the first column.`, `Consider the affection of the
 advent of AI.`, `Consider the history of Google Apps Script.`, `Include
 a value, that you evaluated the activity level of Google Apps Script between 1 and
 100, in the response without overestimating.`, ``, `${filename}`, `The
 format of CSV data is as follows.`, ``, `[Format of CSV data]`, `Created
 date of the question, Title of the question, Score of the question, Tags related
 to the question`, ``, `[CSV data]`, `${headerText}`, `${csv}`, ].join(&quot;\n&quot;); const
 summary = this.generateContent_(q); return { filename, ...summary }; }); return
 DriveApp.getFolderById(this.folderId) .createFile(&quot;summariesOfEachYear.txt&quot;,
 JSON.stringify(res)) .getId(); } /** * ### Description *
 Create a summary of all years. * * @param {String} fileId File ID including
 summaries of each year. * @returns {String} A file ID of the file including
 the created summary. */ createSummaryOfAllYears(fileId) { if (!fileId)
 { throw new Error( &quot;Please set the file ID of the file
 including summaries of each year.&quot; ); } const headerText
 = [&quot;year&quot;, &quot;summary&quot;].join(&quot;,&quot;); const data
 = JSON.parse( DriveApp.getFileById(fileId).getBlob().getDataAsString() ); const
 csv = data.map((e) =&gt; `${e.filename},${e.response}`).join(&quot;\n&quot;); //
 const csv = data.map(e =&gt; `${e.filename},&#39;${JSON.stringify(e.response).replace(/&#39;/,
 &quot;\&#39;&quot;)}&#39;`).join(&quot;\n&quot;); const q = [ `Summary
 the trend from the following CSV data. The CSV data is the summary of trend of questions
 related to Google Apps Script in Stackoverflow every year. The summaries were created
 by Gemini API.`, `Consider the each year of the first column.`, `Consider
 the affection of the advent of AI.`, `Consider the history of Google Apps
 Script.`, `At the last of summary, add all values, that you evaluated the
 activity level of Google Apps Script between 1 and 100, in the response without
 overestimating. Output the values as an array. The array format is [[&quot;year&quot;,
 &quot;activity value&quot;],[&quot;year&quot;, &quot;activity value&quot;],,,].`, ``, `The
 format of CSV data is as follows.`, ``, `[Format of CSV data]`, `year,
 Summary of trend of questions`, ``, `[CSV data]`, `${headerText}`, `${csv}`, ].join(&quot;\n&quot;); const
 { response } = this.generateContent_(q); return DriveApp.getFolderById(this.folderId) .createFile(&quot;summaryOfAllYears.txt&quot;,
 response) .getId(); } /** * ### Description *
 Count tokens of inputted values with Gemini API. * * @param {Object}
 options Object for UrlFetchApp. * @returns {Object} totalTokens */ countToken_(options)
 { const url = `${this.baseUrl}:countTokens` + (this.apiKey
 ? `?key=${this.apiKey}` : &quot;&quot;); const res = this.fetch_({ url, ...options
 }); return JSON.parse(res.getContentText()); } /** *
 ### Description * Generate content with Gemini API. * * @param
 {String} q Text for prompt. * @returns {UrlFetchApp.HTTPResponse|String[]}
 Response from API. When pageToken is used, String[] is returned. */ generateContent_(q)
 { const payload = { contents: [{ parts: [{ text: q }], role: &quot;user&quot;
 }] }; const options = { payload: JSON.stringify(payload), contentType:
 &quot;application/json&quot;, muteHttpExceptions: true, }; if
 (this.headers) { options.headers = headers; } const totalTokens
 = this.countToken_(options); console.log(totalTokens); // Confirm the
 total tokens in the log. const url = `${this.baseUrl}:generateContent`
 + (this.apiKey ? `?key=${this.apiKey}` : &quot;&quot;); const res
 = this.fetch_({ url, ...options }); const obj = JSON.parse(res.getContentText()); if
 ( obj.candidates &amp;&amp; obj.candidates.length &gt; 0 &amp;&amp; obj.candidates[0].content.parts.length
 &gt; 0 ) { return { totalTokens, response: obj.candidates[0].content.parts[0].text
 }; } else { this.retry--; console.warn(&quot;No response.
 Retry again.&quot;); if (this.retry &gt; 0) { this.generateContent_(q); }
 else { console.error(&quot;No response.&quot;); } } } /** *
 ### Description * Request Gemini API. * * @param {Object} obj
 Object for using UrlFetchApp.fetchAll. * @returns {UrlFetchApp.HTTPResponse}
 Response from API. */ fetch_(obj) { obj.muteHttpExceptions = true; const
 res = UrlFetchApp.fetchAll([obj])[0]; if (res.getResponseCode() != 200) { throw
 new Error(res.getContentText()); } return res; } }</pre><h3>5.
 Script for “Generate texts for each year”</h3>This script creates a text file
 including summaries from 2008 to 2024. The file is created in the same folder as
 the CSV files. Of course, you can also see each summary from the file.<pre>function
 process1() { const apiKey = &quot;###&quot;; // Please set your API key. const
 object = { apiKey, folderId: &quot;###&quot;, // Please set the folder
 ID of the folder including 17 CSV files including data from Stackoverflow. }; const
 dg = new DoGemini(object); const res = dg.createSummariesOfEachYear(); console.log(res); }</pre>When
 this script is run, the number of tokens for each year can be seen at the following
 chart.<figure><img alt="" src="https://cdn-images-1.medium.com/max/981/0*bSWLQd16SvydbZbD.png"
 /></figure>This chart reveals that the number of tokens exceeds 32,000 in most
 years. In this context, the Gemini 1.5 API has proven to be a valuable tool.<h3>6.
 Script for “Generate text from all year”</h3>This script creates a text file
 of a summary of the trend of all questions including a tag google-apps-script posted
 from 2008 to 2024. In this case, the result file is created in the root folder.<pre>function
 process2() { const apiKey = &quot;###&quot;; // Please set your API key. const
 fileId = &quot;###&quot;; // Please set the file ID of a text file including summaries
 of each year. const object = { apiKey }; const dg = new DoGemini(object); const
 res = dg.createSummaryOfAllYears(fileId); console.log(res); }</pre><h3>Result</h3>When
 the above scripts are run, the following result is obtained.<pre>## Summary
 of Google Apps Script Activity on Stack Overflow: Based on the provided summaries
 of Google Apps Script questions on Stack Overflow from 2008 to 2024, we can observe
 the following trends: **Early Stage (2008-2009):** * Minimal activity
 as the platform was in its early stages of development and adoption. **Growth
 and Adoption (2010 onwards):** * Gradual increase in questions, indicating
 growing interest and adoption. * Focus on basic functionalities initially, followed
 by more complex and diverse topics as the platform matured. * Active community
 participation and knowledge sharing. **Impact of AI:** * While not
 explicitly evident in the earlier data, the potential for AI integration with Google
 Apps Script is recognized and may influence future trends. **Activity Level:** *
 Estimated activity level between **60 and 70 out of 100**, indicating a healthy
 and active community with room for further growth. **Specific Observations:** *
 Common topics include: * Basic syntax and functionality * Integration
 with Google Sheets, Forms, and other Google products * Email automation *
 Data manipulation * Triggering scripts * Web app development *
 Recurring challenges faced by users: * Authorization and permission errors *
 Understanding specific methods and syntax * Debugging scripts * Optimizing
 script performance **Overall, the data suggests a steady and ongoing interest
 in Google Apps Script, with a growing user base and increasingly complex use cases.
 While AI integration is not yet a dominant theme, it has the potential to influence
 future trends and applications.** **Estimated Activity Levels:** Based
 on the analysis of each year&#39;s data, here&#39;s an array summarizing the estimated
 activity levels of Google Apps Script on Stack Overflow: ``` [ [&quot;2008-2009&quot;,
 10], // Estimated low activity due to early stage [&quot;2010-2011&quot;, 30],
 // Moderate activity during early growth [&quot;2011-2012&quot;, 60], // Increasing
 activity and diversification of topics [&quot;2012-2013&quot;, 60], // Steady
 growth and expanding capabilities [&quot;2013-2014&quot;, 60], // Moderately
 active and engaged community [&quot;2014-2015&quot;, 65], // Consistent activity
 with focus on both basic and advanced functionalities [&quot;2015-2016&quot;,
 75], // Healthy and active community, diverse topics covered [&quot;2016-2017&quot;,
 70], // Consistent activity, focus on integrations and web app development [&quot;2017-2018&quot;,
 70], // Steady interest, diverse use cases, and demand for advanced functionalities [&quot;2018-2019&quot;,
 65], // Consistent activity, potential for future AI integration [&quot;2019-2020&quot;,
 65], // Sustained interest, potential influence of AI in automation [&quot;2020-2021&quot;,
 65], // Consistent activity, continued relevance and adoption [&quot;2021-2022&quot;,
 75], // Vibrant and active community, growing adoption and complex use cases [&quot;2022-2023&quot;,
 75], // Consistent activity, potential for AI integration in specific areas [&quot;2023-2024&quot;,
 65], // Steady interest, focus on basic functionalities and integrations ] ``` **Note:**
 These activity levels are estimations based on the provided data and general knowledge
 of Google Apps Script&#39;s adoption and development. A more precise assessment
 would require access to a larger dataset and more detailed information about question
 topics and user engagement.</pre>When the activity level is evaluated by Gemini
 1.5 API for each year is exported as a chart, it becomes as follows.<figure><img
 alt="" src="https://cdn-images-1.medium.com/max/981/0*Y_vIz1H0Xoz6yVBb.png" /></figure>The
 above results confirm various insights about the trend of Google Apps Script on
 Stack Overflow. Additionally, the Gemini 1.5 API emerged as a valuable tool for
 data analysis.<h3>Note</h3><ul><li>The top illustration was created by <a href="https://gemini.google.com/">Gemini</a>
 with giving the abstract.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c6b448e36461"
 width="1" height="1" alt=""><hr><a href="https://medium.com/google-cloud/analyzing-trends-of-google-apps-script-from-questions-on-stackoverflow-using-gemini-1-5-api-c6b448e36461">Analyzing
 Trends of Google Apps Script from Questions on Stackoverflow using Gemini 1.5 API</a>
 was originally published in <a href="https://medium.com/google-cloud">Google Cloud
 - Community</a> on Medium, where people are continuing the conversation by highlighting
 and responding to this story.'

Language

Active

Ricc internal notes

Ricc source

Show this article Back to articles