AppsScriptPulse

Category: Sheets

AdvancedSheetsSolutions

How to use Flutter with Google Sheets, Apps Script, and Codemagic | Codemagic Blog

Tags: ,
| | 0 Comments |

In this article Jahswill Essien describes how to use Google Sheets with Flutter via Google Apps Script.

This article assumes the following:

  • You have experience using the basics of Flutter and can call APIs and read an existing codebase
  • You have little to no understanding of Google Apps Script
  • You have some experience using Google Sheets or spreadsheets in general

I’m sure many of you reading this post will already be familiar with Google Apps Script and have little/no experience deploying Flutter apps but I also know a lot of Google Apps Script developers are keen to try new stuff.

If deploying mobile apps is a thing that interests you I’d highly recommend also looking at Google no/low code development platform, AppSheet, which enables you to quickly create/deploy apps often using Google Sheets as a backend. If, however, you need custom graphics or pixel perfect UI, solutions like Flutter would be my personal starting point. Enjoy!

Source: How to use Flutter with Google Sheets, Apps Script, and Codemagic | Codemagic Blog

BeginnerSheetsSolutions

Get hidden or visible Google Sheet tabs with Google Apps Script

Tags: ,
| | 0 Comments |

Learn how to list hidden Google Sheets tabs with Google Apps Script – CODE and VIDEO tutorial with script explainers.

This post from Scott Donald is an opportunity to learn about how you can work with hidden tabs in Google Sheets with Apps Script. For beginners this post is also an opportunity to learn about the JavaScript reduce method as a way to iterate through data. As well as the code and supporting video the author Scott Donald has some related projects where you can see this solution in action.

Source: Get Hidden or Visible Google Sheet Tabs with Google Apps Script – Yagisanatode

BeginnerDriveSheetsSolutions

Bulk convert Google Sheets to PDFs using Google Apps Script

Tags:
| |

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
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.

Source: The Gift of Script: Bulk convert Google Sheets to PDFs

BeginnerSheetsSolutions

Create automated backups of Google Sheets with Google Apps Script

Tags: , ,
| | 0 Comments |

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)

Source: Create automated backups with Apps Script

IntermediateSheetsSolutions

Uploading multiple records with the Salesforce API using Google Apps Script

Tags: ,
| | 0 Comments |
Image credit: Eduardo Ramon Resser

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.

Source: Uploading Multiple Records Via Salesforce API Using Google Apps Script

BeginnerDriveSheetsSnippetSolutions

Import CSV data into Google Sheet using Google Apps Script

Tags:
| | 0 Comments |

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
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.

Source: The Gift of Script: Import CSV data into Google Sheet files

BeginnerSheetsSolutionsV8

An automatic random team maker using Google Apps Script

Tags: , , ,
| | 0 Comments |

In this post, we’re going to look at a script to be able to make even teams from a selection of players. I play football every Sunday and every week my friends sign up to play and most weeks there are different players playing, so we end up having to make the teams, and trying to do it as fairly as we can, which inevitably takes some time.

Nice ‘weekend project’ from Baz Roberts highlighting how Google Apps Script can be used to do a weighted random shuffle of data. The post includes a line-by-line explanation of what the script is doing and a great resource if you are at the beginning of your Apps Script journey.

Source: Automatic Team Maker — Apps Script (also available on bazroberts.com)

FormsIntermediateSheetsSolutions

Creating a custom workflows using Google Forms and Google Sheets for provisioning virtual machines (VM) in Google Cloud

Tags: ,
| | 0 Comments |

Is it possible to integrate Google Workspace tools such as Calendar, Sheets, and Forms with Workflows? For example, can you trigger a workflow from a Google Form or a Sheet? Turns out, this is not only possible but also easier than you might think. Let me show you how with a sample use case.

One of the great things about Google Workspace is there is never ‘one’ solution, there is enough flexibility for you to implement one of many workflows. A case in point is this post highlighting a Google Forms/Sheets powered approval system for allocating virtual machines (VM) to your users. As part of this the workflow includes calling the Compute Engine API to create the VM in Google Cloud.

Source: Triggering Workflows from Google Sheets

AppSheetIntermediateSheetsSolutionsV8Web App

AppSheet/PayPal integrated payment solutions and tips on handling data in Google Sheets

Tags:
| | 0 Comments |

In this post I’ll provide an overview of how PayPal was integrated into AppSheet with the help of Google Apps Script. Even if you are not interested in payment integrations this post also hopefully pulls together useful tips, best practices and code patterns for reading/writing data to Google Sheets.

Hopefully this post illustrates not only just a method for integrating a PayPal payment processor into an AppSheet app, but also a method which can generally be used to extend AppSheet functionality with Google Apps Script powered Web Apps.

As a bonus you also get some of my top tips for interacting with data in Google Sheets including efficiently reading/writing data for multiple users without concurrent overwrites.

Source: AppSheet/PayPal integrated payment solutions and tips on handling data in Google Sheets

BeginnerDriveSheetsSnippetSolutions

Search Google Drive for ‘Shared with me’ with Google Apps Script

Tags:
| | 0 Comments |

Search Google Drive for all of the files found in the Shared with me space and collate the results into a Google Sheet.

Search Google Drive 'Shared with me' and collate the results
Search Google Drive ‘Shared with me’ and collate the results

It will collate the following information into a Google Sheet:

  • The file name as a direct clickable link,
  • The file ID,
  • The type of file eg PDF, Google Sheet/Doc,
  • The file creation date,
  • The file last updated date,
  • The folder path,
  • The file owner.

Source: The Gift of Script: Search Google Drive for Shared with me