Automatically initialize values with AppScript

Ask for Help
1

Hi there, I wanted to share a basic Google App Script usage here for default values initialization in profiles for example.

So let’s say every new user falls into a User table and I want some data like a role to be set by default.

Capture d’écran 2020-11-14 à 17.37.02

Here’s my App script, called on every change on my Spreadsheet.

function prepareNewCustomer(e) {
  // Check is the event is a customer change or not, if not, exit.
  if (!isCustomerChange(e))return;
  // Get Customer Sheet
  let customerSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Users");
  // Get the last row number
  let lastRow = customerSheet.getLastRow();
  // Get all Row without anything in F column 6, until last row, removing the header
  let roleRange = customerSheet.getRange(2,6,lastRow-1);
  let roleValues = roleRange.getValues();
  
  for (var row in roleValues){
    for (var col in roleValues[row]){
      // WARNING we are operating at -1 for the row
      // -1 because the array is starting at 0 and not 1
      // -1 again because of header removal
      if (roleValues[row][col]===""){
        let sheetRow = Number(row)+2;
        customerSheet.getRange("F"+sheetRow).setValue("Client");
      }
    }
  }
  
}

function isCustomerChange(event) {
  let activeSheet = event.source.getActiveSheet().getName();
  return (activeSheet === "Users") ? true : false;
}
3 Likes
2

Just wanna offer an alternative way here. I assume you would have an onboarding process with an increment button. With the compound action and the set columns integration, I believe you can set the client value the same way as you do here.

1 Like
3

Well, in my case, you have to sign-in for using the app with an email and you can fill your profile if you want to save time when ordering, but not mandatory. It’s just to have a user instance to save your cart and being able to show your cart items anytime.

So the profile is mostly empty (as you saw on the screenshot).

The compound action could have worked otherwise you’re right (except you need to replace all your “button forms” or “edit records” with something you create to be able to use the button linked to a compounded action I believe and I’m not a huge fan of this so far. It kinda breaks the design patterns that people had hard time to learn (the top right submit button lol).

1 Like
4

I see. Just one more thing, if you only have 2 types of users, like I see in the screenshot, what I usually do in my apps is just to use conditions as role is empty or not. Or if I need to display the client value then an If Then Else.

Understand you may want that value so you can do extra work within the Sheet, thanks for sharing.

1 Like
5

Good point indeed :smiley: And it remove the need for this little AppScript ahah. But I’m pretty sure I’ll need an admin, manager and client later on.

1 Like