Bulk convert Google Sheets within a given Google Drive folder into PDFs and optionally delete the original file.
Bulk convert Google Sheets to PDFs using Apps Script
Features
Maximum runtime – in order to prevent the tool from reaching the limits imposed by Google you can adjust the number of minutes the tool can run for. Change this in the ‘GlobalVariables.gs’ file in the Script Editor.
Continue from where it left off – if you have a lot of Google Sheets to convert and the above runtime is reached the tool will save its progress and prompt you to run it again, avoiding any file duplication.
HTML popup – as well as the ‘Log’ sheet the tool displays a direct popup to the user if it encounters a problem.
PDF counter – after successfully running the tool will include the number of PDFs created as part of the success popup to the user.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
This is a workaround for checking the existence of file ID in Google Drive without both the access token and API key.
When you want to check whether the file of the file ID is existing in Google Drive, generally, you might use Drive API and Drive service (DriveApp) of Google Apps Script. In this case, the scope of Drive API is required to be used. By this, the access token and the API key (in the case of publicly shared files) are required to be used. But, there might be a case that the available scopes are limited. In this post, I would like to introduce a workaround for checking the existence of file ID in Google Drive without both the access token and API key.
This workaround could have been a nice addition to a recent project I was working on to audit a bunch of Google Drive file IDs. As noted in this post a big benefit of the approach is there is no need to include Google Drive authentication scopes to your project. See the source post for the code and explanation.
You’ve built a great add-on for the Google Workspace ecosystem (Google Docs, Google Sheets, Gmail & co.). It’s getting a lot of traction and you feel like you can monetize it. This is the guide I wish I had found a few years ago when I was researching how to monetize our own add-on for Google Sheets.
Here is a very concise and incredibly useful guide written by Corentin Brossault, co-founder and CTO at Mailmeteor, on the practical steps you can take if you are interested in monetizing your Google Apps Script solution. The post includes some code snippets and guidance on protecting personal information of your users, storing licence data in Firebase and integrating with Stripe as a payment processor.
Over the past year, we’ve added more functionality to AppSheet, extending how it can maximize the power of Google Workspace through integrations with products such as Gmail, Google Drive and Apps Script. To improve the experience for app creators and users, we’re excited to introduce in public preview AppSheet databases, a built-in database for citizen developers to easily and securely manage their data.
During public preview, access to AppSheet databases will be enabled by default for everyone but it will not affect existing apps. Use of this feature in public preview will be included at no additional cost in your AppSheet subscription plan, but limited to 10K rows per table, 20 tables per database and 20 databases per user. Please note that these limits may change when the feature is generally available.
For people who tuned in to TU3.5: New workflow solutions with Tables and Google Apps Script back in 2020 AppSheet databases will look very familiar. Since that episode the Area 120 team behind the Tables beta moved into Google Cloud and specifically AppSheet.
The big question for me is whether AppSheet databases can find a sweet spot for citizen developers looking for something with more performance than a Google Sheet and easier to setup than a Google Cloud project. In public preview AppSheet databases are limited to 10,000, which interestingly is the same limit as the old paid version of Tables.
Ultimately this may however be less able size and performance but a data solution that the AppSheet team has full control, particularly removing a reliance on features developed by the Google Sheets team.
The problem comes when you have to load that info in your app. … The more the data, the more time to load. You could use Apps Script to schedule backups of your data.
A nice introduce into Google Apps Script with an opportunity to learn how to archive data from one Google Sheet to another. This solution comes from Mozart García who is more often seen on YouTube producing short Google Apps Script how-to in Italian (Bravo! Mozart on recently passing 4K subscribers)
Chances are that you may need to display user’s picture profiles in your applications. However, this API is part of the Directory API, which is part of Google Workspace Admin area. And chances are that your Organization has simply not giving you the rights to use Admin APIs
Sometimes within your Google Workspace domain you’d like to include account profile images. A number of Google Workspace APIs, like the Drive API, will include profile pictures in the response object. When this isn’t the case this post by Turhan Oz is a nice reminder that the dedicated People API can be used to get profile images. Click through to the source post to read more and sample code.
There is a way to use Google Docs as a programming IDE and run JavaScript code inside the editor … It is no replacement for a dedicated IDE like Visual Studio code but Google Docs can be used as a JavaScript playground to quickly run code snippets.
A familiar story I hear is people with no formal coding experience starting their developer journey with Google Apps Script. I find being able to run small snippets of code are a great way to learn and a number of sites like W3 Schools and also increasingly Stack Overflow include the ability to ‘try it yourself’ with inline code runners.
If you are looking to support users getting started with Google Apps Script Amit Agarwal has this little code snippet which lets you inline/run Google Apps Script code in Google Docs. There are both limitations and considerations with this approach but as a quick way for learners to run and collaborate on small snippets, which can be contextualised with explanatory text/resources, it’s a nice starting point. Check out the source link for more details.
Sometimes, when using Salesforce, we come across the situation of needing to upload several object records at once, such as accounts, contacts, tasks, etc.
There are several ways to resolve this issue. One approach that can be efficient is through Google Sheets, especially if you want to “convert” each row in a table into separate records in Salesforce.
In the example of this article, I will talk about an implementation for bulk exporting of tasks using information contained in a table in Google Sheets using the Salesforce API.
This post on interacting with the Salesforce API highlights the tricky situation of handling different user credentials. The author comes up with a clever solution of using user owned Google Docs as a way to store different identities. Another approach is to use Google Apps Script’s Properties Service. The Properties Service includes an option to store User Properties which are only accessible to the user running the script. The downside of this approach is you then have to create UI for the user to input properties.
Loop through CSV files in Google Drive and extract their contents into specific areas in Google Sheet files.
CSV File template to copy data into
The following Google Apps Script is designed to loop through a folder of CSV files in Google Drive, extract the data, create a Google Sheet file per CSV, and insert the data into specific rows/columns.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
If you are a Google Workspace and Trello power user like me, I’m sure the thought has come across your mind of connecting your Google Apps to Trello, but every time you search for a solution you get an ad for Zapier or IFTTT. I try to avoid these solutions because they are great at making an initial connection, but if you ever want to do anything advanced, it will require you to sign up for a premium subscription. After searching and not finding answers, I decided just to try and build it for myself.
If you are a beginner to Google Apps Script this is a nice tutorial if you are interested in learning about connecting with other services, in this case Trello. Hopefully this post also illustrates that coding aspect of connecting with other services using Google Apps Script can be straightforward once access had been setup on the third party site.
