AppsScriptPulse

Official News, Solutions, Google Analytics, Sheets

How to build a Google Analytics Dashboard with Apps Script and Google Sites – Sheets to Apps

Tags:
| | 0 Comments |

Using Google Analytics and looking for a new way to aggregate, store, and efficiently organize the data from your website in a Google Sheet? In this episode of Sheets to Apps, we show you how to combine the Google Analytics add-on with Apps Script, resulting in the automation of cohesive data summaries that are compatible with Google Sites.

In this latest episode Alexandrina Garcia-Verdin demonstrates how the Google Analytics Add-on can be combined with macros to automate regular reporting. The video description has links to all the resources you need to set this up.

Whilst ‘Sheets to Apps’ videos are targeted at low/no coders as an Apps Script Developer I always find it useful to have a look at the code to see how it is done, as more often than not there will be a technique or method I’ve not come across. In the case of this example my new discovery was the .autoFill() and after browsing the documentation, .autoFillToNeighbor() methods in SpreadsheetApp. These methods allow you to replicate the auto-fill functionality users have in Google Sheets.

Another discovery was the various .setOption() calls when building the charts. I’ve used .setOption() in projects in the past and the issue I’ve had is navigating the long list of options you can use for various chart types. Using the macro recorder seems like a great way to capture all the options you want to add to your script project.

Official News, Solutions, Sheets

How macros can be used to build Apps Scripts for cleaning data – Sheets to Apps

Tags:
| | 0 Comments |

Cleaning up data from downloaded accounting reports or from other systems can take up valuable time and resources. In this episode of Sheets to Apps, we’ll walk you through adding automation to your monthly Excel accounting data that records your steps into a script that you can run repeatedly with Google Sheets macro recorder.

From the video description you’ll find all the links you need to the example Google Sheet, instructions and more

Official News, Solutions, Forms, Gmail

Auto respond to feedback via Apps Script – Sheets to Apps

Tags:
| | 0 Comments |

Course evaluations can be a bit overwhelming when receiving mass amounts of feedback, but thanks to Apps Script this is a problem of the past. In this episode of Sheets to Apps, we’ll show you how to create and use an Apps Script for Google Sheets that allows you to respond to feedback on Google Forms via email.

In a recent episode of Sheets to Apps Ben Collins’ clever ‘Respond to feedback from students in a course’ in the G Suite  Solutions Gallery is featured. See the YouTube video description for related links.

Official News, Solutions, Groups, Sheets

Streamline access to resources for vendors via Google Sheet – Sheets to Apps

Tags:
| | 0 Comments |

Need to share a variety of documents with vendors? Don’t want to share access one by one? In this episode of Sheets to Apps, we’ll cover how to use an Apps Script in a Google Sheet to automatically add members to a Google Group, helping you share documents at a large scale that will save you time.

We’ve previously shared the Medium post that provides more details for setting up this solution. If you missed that post or were unclear Alexandrina Garcia-Verdin, better known in the community as AGV, provides a very useful overview.

The code and other resources presented in this episode are available from the video description.

Solutions, Sheets

Grant access to resources 🚣 from a Google Sheet in G Suite

Tags:
| | 0 Comments |

Use a Google Group to work cross-functionally with vendors, partners, customers, and volunteers outside of your domain, and scale access to work assignments and leverage security controls.

Share documents, events, and communications with users outside (or inside) your G Suite domain.

Source: Grant access to resources 🚣 from a Google Sheet in G Suite

Official News, Solutions, Forms, Sheets

Automate onboarding of company resources from a Google Sheet – Sheets to Apps

Tags:
| | 0 Comments |

In this episode of Sheets to Apps, we cover how to give newcomers access to resources via Google Forms and an Apps Script that lives in a Google Sheets, making on-boarding new employees simple and easy.

The code and other resources presented in this episode are available from the video description.

Official News, Solutions, Forms, Sheets

Manage new employee equipment requests within Google Sheets – Sheets to Apps

Tags:
| | 0 Comments |

In today’s episode of Sheets to Apps, @TechandEco will show you how to manage new employee equipment requests with Google Sheets.

See the video description for all the links you need including the reference documentation.

Solutions, Forms, Gmail, Sheets

Workflow to collect and approve budgets using Apps Script in Google Sheets

Tags:
| | 0 Comments |

Gathering inputs from multiple sources and then taking action on each becomes taxing when the task needs to be realized with frequency and with multiple stakeholders. An example is quarterly or annual budget approvals for a finance team.

In this article the author will share how to use an Apps Script in a Google Sheet to automatically create a budget submission form that you can share with end users, and when their responses arrive in the sheet, you can collaborate with other reviewers to send emails in bulk depending on whether you are approving, rejecting, or asking for more information about their request. The emails use a Google doc as a template that pulls information from the sheet such as a user’s name, the budget values they entered, or special comments.

Source: Workflow to collect and approve budgets using Apps Script in Google Sheets

Solutions, Sheets

Faster budgeting with a Google Apps Script custom function

Tags:
| |

Companies, organizations, households, students, etc all benefit from budget planning in order to optimize savings, because you forecast your total expenses, and then closely monitor the true cost in order to course correct and you reach a savings goal.

In this article the author walks us through how to create our own custom function so we can prepare our annual household budget by entering our expenses in one row and marking their frequency in another (ex: annual, monthly, weekly, daily, or only one time). When we have rows and rows of expenses with different time periods, creating our own formula with a Google Apps Script helps us save a lot of time.

Source: Faster budgeting with a Google Apps Script custom function

Solutions, Sheets

Cleanup exported data automatically with Apps Script and advanced formulas in Google Sheets

Tags:
| | 0 Comments |

You may be exporting data from an accounting tool into an .XLS format (Excel spreadsheet) every month, and then manually searching, cleaning, and replacing values. More specifically you probably spend a lot of time finding and replacing duplicates, and performing advanced operations such as a VLOOKUP or a QUERY function in order to pull data from other sheets or digging through records that use unfriendly date timestamps.

In this article TechandEco walks us through how to automate this monthly task by using a Google macro and a custom menu, and invite you to try it out yourself. In the future, you can convert your Excel into a Google Sheet by dragging it or uploading it into a folder in your Google Drive>then second click the file to view its options > open as > Google Sheet.

Source: Cleanup exported data automatically with Apps Script and advanced formulas in Google Sheets