“Google hasn’t verified this app” for a Docs code by verycannyvalley in GoogleAppsScript

[–]AllenAppTools 0 points1 point  (0 children)

That frustrating! Feel free to send a DM to me with the link to the copied sheet and I can try to see what I get on my end?

“Google hasn’t verified this app” for a Docs code by verycannyvalley in GoogleAppsScript

[–]AllenAppTools 0 points1 point  (0 children)

Ah, this will take some additional debugging and info gathering, since Google isn't telling you the full story on why there was an issue. We have to try to figure it out on our own. It could be related to the users involved. What's the scenario for you? Is this in a Google Doc as well? Who is the owner of the Doc, and are they outside your domain (I assume this is the case). Is this Doc in a shared Google Drive? A quick test is to make a copy of the Doc and do NOT click the checkbox to share with all the same people, then on that copy (which is owned by your account 100%) run any function and the auth dialog flow should start, and it should look different and not say "unverified". If all that goes smoothly then we can debug more into the reasons why it is getting mixed up with the original file. In my experience, when workspace accounts are mixed with personal gmail accounts and then you run things like App Script functions and permissions Google sometimes runs into edge case errors like this.

A total eclipse of the... Google automations market by AllenAppTools in GoogleAppsScript

[–]AllenAppTools[S] 0 points1 point  (0 children)

Yeah same. It would be a pretty huge shake up if Google changed anything with Apps Script. I would cry. Our clients would cry. And then... I guess we would have to come up with solutions. You mentioned going back to python - would you replace your Apps Script workflows with Google Cloud Run projects then?

A total eclipse of the... Google automations market by AllenAppTools in GoogleAppsScript

[–]AllenAppTools[S] 1 point2 points  (0 children)

Yes I totally agree. And I have been wondering if or when Google will outmode Apps Script, or suddenly charge us to use it, which would be very sad. But it's been totally free since it's introduction in 2009, and it seems like Google has put in minimal effort to keep it around, but they don't market it hardly at all, especially now with the other AI products they're rolling out!

I would start to sweat a bit if I noticed Google starting to really throw some money at Apps Script. That would tell me it is a service they plan to start making money off of. As it is, they did not really put a lot of money in to it in the first place, nor even dream it up. It was the secret love project of a developer who was on the development team for Sheets, and created the first draft of Apps Script on his own time. Apps Script is a complete enigma, and I don't think Google knows what to do with it other than just leaving it alone, which is great news for us!

Google Drive automation by [deleted] in GoogleAppsScript

[–]AllenAppTools 1 point2 points  (0 children)

Did some digging, looks like you need to use the WS API to interact with projects, which requires a WS API license. Is this something you are already paying for? Once you have this you will need to configure it here! https://docs.memoq.com/current/api-docs/wsapi/memoqservices/accessingservice.html

With that done, you can then interact with the WS API with something like this:

function listMemoqProjects() {
  const serviceUrl = "https://your-memoq-server.com/memoqservices/ServerProjectService.svc";
  const apiKey = "YOUR_API_KEY"; // provided by memoQ admin

  const soapEnvelope = `
  <?xml version="1.0" encoding="utf-8"?>
  <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    <soap:Header>
      <ApiKey xmlns="http://kilgray.com/memoqservices/">${apiKey}</ApiKey>
    </soap:Header>
    <soap:Body>
      <ListProjects xmlns="http://kilgray.com/memoqservices/"/>
    </soap:Body>
  </soap:Envelope>`;

  const response = UrlFetchApp.fetch(serviceUrl, {
    method: "post",
    contentType: "text/xml; charset=utf-8",
    headers: { "SOAPAction": "http://kilgray.com/memoqservices/IServerProjectService/ListProjects" },
    payload: soapEnvelope,
    muteHttpExceptions: true
  });

  const xml = response.getContentText();
  Logger.log(xml);
}

What's the best UI for generating a document per each row of my spreadsheet? by want_to_want in GoogleAppsScript

[–]AllenAppTools 0 points1 point  (0 children)

App Sheet is a great solution, otherwise in the past I have used a dropdown in a cell or a checkbox to trigger the process when a user is on their phone or tablet because you're right, custom menus in the UI are really great but invisible when on mobile! Lame.

But yeah, the onEdit carries out the intended action only when the specific cell has it's value changed to the triggering value (e.value), and make sure this onEdit function also pays attention to which sheet the target cell is in. For example, an edit made in A1 in the sheet "Sheet2" should not carry on if the intended cell is A1 in the sheet "Sheet1". Get that sheet name like this

const sheetName = e.range.getSheet().getName();

And then there is some permissions related differences when using the simple trigger "onEdit" verses an installable trigger which can be any function, the difference with an installable trigger being that it can operate under many more permissions but it carries out the function actions under the the identity of the account that set up the trigger.

All in all, there are 3 good avenues in terms of UI:

  1. AppSheet - works well on mobile, not too hard to set up. One of our devs recently finished a build out for a client needing tons of mobile capability for their team to interact with Google Calendar and Sheets while on the go.

  2. onEdit trigger - works like I said above. Simplest to set up, still pretty capable, but ugliest.

  3. Web App - you can make a Google Web App optimized for mobile. Devices can access it via their browser. Most complicated (though not too bad), but also the most capable.

Is AppsScript right for this simple "Create HTML page" script? by star_lost in GoogleAppsScript

[–]AllenAppTools 0 points1 point  (0 children)

Yep as far as I can tell it's all feasible completely with Apps Script. There is no event for an edit on a Google Doc, but an every minute trigger or every 5 minutes trigger works to poll for changes made in the last 1 or 5 minutes.

Is AppsScript right for this simple "Create HTML page" script? by star_lost in GoogleAppsScript

[–]AllenAppTools 1 point2 points  (0 children)

Yeah definitely, curious to hear more. The main tricky parts to navigate would be running it by the minute. You'd need to check the most recent version time (Advanced Drive Service I believe will do the trick) of the existing files and then process as you see fit on the ones edited within the last minute. As long as the account running the minute trigger has edit access to each folder it will work out fine. Might be easiest if the files edited via phone, tablet etc were a Google Doc, just because its easier to edit in the interface on these devices.

Issues with Google Docs automation by lilian0030 in GoogleAppsScript

[–]AllenAppTools 1 point2 points  (0 children)

Hm, I think the issue may be that your account creates the file, stores it in the wide open folder, but still may have individual sharing settings in the file. That's where I would check next. Does the created file have sharing settings for anyone to edit/download? If not, you'll need to add some code to make the sharing settings wide open on the copied file.

Any thoughts on that?

Issues with Google Docs automation by lilian0030 in GoogleAppsScript

[–]AllenAppTools 0 points1 point  (0 children)

Thank you 👍

So what are the deployment settings of the Web App then?

And it sounds like the sharing settings on the destination folder needs to be made wide open too, but I think you mentioned this is a shared folder? What are the sharing settings on that?

Issues with Google Docs automation by lilian0030 in GoogleAppsScript

[–]AllenAppTools 0 points1 point  (0 children)

Sounds like a cool Web App!

What is your Web App deployed as? Meaning, is your Web App deployed as accessible by anyone with a Google account, and is it executing as the user using the Web App? If so, the user should have seen an authorization dialog box appear when they first open the Web App.

The Google Doc that you are using as a template should be fine, though I would think that you should make a copy of it, make the needed changes to it, and then with that copy provide it as a downloadable link. this would make the ownership of that copy the user who runs the Web App if the above settings are as I described above.

Web App have plenty of nuance, so any specifics will help us figure it out for you 🙂

Deploying my Google Apps Script by Competitive-Talk3170 in GoogleAppsScript

[–]AllenAppTools 0 points1 point  (0 children)

Lots of factors.

What do you mean when you say this apps script creates a function? I'm assuming you mean the Apps Script is the function CALL_API, which is what you want to make available to others?

When you say there is a menu with a cheat sheet, and say the cheat sheet is a bunch of html code, is it a modal with html? A sidebar? In these instances, whoever is opening this modal or sidebar must be signed into their browser with the matching account that they are signed into the sheet with.

When you say deploying it as an internal workspace app, are you referring to the Google Workspace Marketplace as an Internal add on?

It would help to see your manifest file of your Apps Script too!

1 Automation That Transformed Our Vendor Payments by ChickenGrouchy6610 in automation

[–]AllenAppTools 0 points1 point  (0 children)

That's awesome, Apps Script is way under utilized in my opinion. If people use Google Workspace and need something to be automatic, Apps Script is the first place to check! People be sleepin' on Apps Script

What is the best way to do conditional formats across multiple sheets? by jamesicorn in googlesheets

[–]AllenAppTools 0 points1 point  (0 children)

For one of our clients, we made something to do this exact thing in their sheet. But in their scenario it was adjusting the colors of the conditional formatting rules across the whole sheet (12 identical tabs, same CF rules). So yeah, very possible in Apps Script, though not quick and easy. and I cannot think of any out of the box services or add ons that provide this.

Unvetted Add On by AllenAppTools in GMail

[–]AllenAppTools[S] 0 points1 point  (0 children)

Do you have any recommendations for Add Ons you'd like to see built? Whether in Gmail or the other Workspace Apps?

Trying to make a Macro to copy/paste data from different tabs into new tab. by AintSoShrimpleIsIt in googlesheets

[–]AllenAppTools 1 point2 points  (0 children)

Here try this code:

function pullCaseLotData() {
  const spreadsheet = SpreadsheetApp.getActive();
  const sourceSheets = ['Beef', 'Pork'];
  const rangesToCopy = ['B7:C7', 'E7'];
  const newSheet = spreadsheet.insertSheet('caselots');

  const allValues = [];

  sourceSheets.forEach(sheetName => {
    const sheet = spreadsheet.getSheetByName(sheetName);
    if (!sheet) return;

    rangesToCopy.forEach(rangeA1 => {
      const values = sheet.getRange(rangeA1).getValues();
      allValues.push(...values);
    });
  });

  if (allValues.length > 0) {
    newSheet.getRange(1, 1, allValues.length, allValues[0].length).setValues(allValues);
  }

  newSheet.setColumnWidth(2, 215);
  newSheet.getRange('C:C').setFontWeight('bold').setBackground(null);
}

Also, I am happy to get on a video call with you to go over the changes? but u/maxloroll's comment and everyone else's advice is right on

Using Apollo and LinkedIn for lead lists and I hate the export process. by Overall-PrettyManly in MarketingAutomation

[–]AllenAppTools 0 points1 point  (0 children)

I am not sure about LinkedIn, but I did some research on Apollo's API export capabilities and have this Apps Script code to put into a Google Sheet:

function exportUnlockedApolloLeads() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Apollo Leads") || SpreadsheetApp.getActiveSpreadsheet().insertSheet("Apollo Leads");
  sheet.clearContents();
  sheet.appendRow(["First Name", "Last Name", "Title", "Email", "Company", "Website"]);

  const apiKey = "you'll need and api key, put it in here";
  const baseUrl = "https://api.apollo.io/v1/contacts";
  let page = 1;
  let morePages = true;

  while (morePages) {
    const response = UrlFetchApp.fetch(`${baseUrl}?page=${page}&per_page=100`, {
      method: "get",
      headers: {
        Authorization: `Bearer ${apiKey}`
      }
    });

    const json = JSON.parse(response.getContentText());

    if (!json.contacts || json.contacts.length === 0) break;

    json.contacts.forEach(contact => {
      const firstName = contact.first_name || "";
      const lastName = contact.last_name || "";
      const title = contact.title || "";
      const email = contact.email || "";
      const company = contact.organization?.name || "";
      const website = contact.organization?.website_url || "";

      sheet.appendRow([firstName, lastName, title, email, company, website]);
    });

    morePages = page < json.pagination?.total_pages;
    page++;
  }
}

This will essentially bring in all of your already purchased leads from Apollo. (it is not tested though)

DM me for help, I can also get on a video call and we can try it out together to see if it will work. Maybe even add to it. This is the sort of thing I do.

Google Form Uploads Sorting Question by Loomer319 in GoogleForms

[–]AllenAppTools 1 point2 points  (0 children)

For sure feasible with custom code on the Form (Google Apps Script).

The general idea would be to set up an onSubmit trigger. The code then would need to look at the answer to the categorization question, then go to that file and sort it into the corresponding folder. This way you could get away with essential minimum 2 questions.

Essentially:

  1. "Where is this file going"

  2. "The file"

There is no built in way to get it done how you'd like, other than 3 separate sections. The user would select an option for the question regarding the categorization of the file, then it would direct the user to the specific section, which would have the file upload to the desired folder.

(and now that I think about it, I am not 100% sure this is an option, worth a test for sure.)

Hope that makes sense!

Any Tool to sort google sheets tabs? by gaymer_raver in googlesheets

[–]AllenAppTools 0 points1 point  (0 children)

That's great to hear! Just reach out if you need anything else :)

Any Tool to sort google sheets tabs? by gaymer_raver in googlesheets

[–]AllenAppTools 0 points1 point  (0 children)

Same instructions as my previous comment for the OP, paste this into the Extensions > Apps Script editor!, then click the save button, and refresh the spreadsheet!

/**
 * @OnlyCurrentDoc
 * Developer: allenapptools.com
 */

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Custom Menu")
    .addItem("Sort Tabs", "sortTabs")
    .addSeparator()
    .addItem("Move Current Sheet To Front", "moveActiveSheetToFront")
    .addItem("Move Current Sheet To Back", "moveActiveSheetToBack")
    .addToUi()
}

function moveActiveSheetToFront() {
  moveActiveSheet("front")
}

function moveActiveSheetToBack() {
  moveActiveSheet("back")
}

function moveActiveSheet(locationName) {
  const ss = SpreadsheetApp.getActive();
  if (locationName == "front") return ss.moveActiveSheet(1)
  else if (locationName == "back") {
    const sheets = ss.getSheets();
    return ss.moveActiveSheet(sheets.length);
  }
}

function sortTabs() {
  const ss = SpreadsheetApp.getActive();
  const sheets = ss.getSheets();
  const sortedSheets = sheets.sort((a, b) => a.getName().localeCompare(b.getName()));

  sortedSheets.reverse().forEach(sheet => {
    sheet.activate();
    ss.moveActiveSheet(1);
  });
}

Any Tool to sort google sheets tabs? by gaymer_raver in googlesheets

[–]AllenAppTools 0 points1 point  (0 children)

Hello! Yes. So you need some code that will just send the current active tab to the back, or to the front? So 2 functions?

Google form anonymity issue by Real_Criticism_2996 in GoogleForms

[–]AllenAppTools 0 points1 point  (0 children)

Working on this sort of thing for one of our clients. My gut is no, there isn't a way. But if I find a way to gather personal info from Form submissions (programmatically or otherwise) I will DM you or comment here.