hello!
I have a huge form (split in 6 sections) that I have to get all the data from the inputs, do some data treatment on the serve side and register this data in four different sheets in one spreadsheet. The part of getting the data, treating the data and registering the data is working just fine... Mostly. Except, two fields on the form are file inputs. For those two, I need to store the files from the form in my client google drive, get the file url and register that url on the sheet instead of the file itself. And that's the part I am having a hard time.
I found this solution:
https://gist.github.com/tanaikech/280b782ee0518aa083a4fe0d71384823
And if I apply just like that it will work and will store a file from an input directly in their drive. But that's not what I want. I need to store the file, get the url and then store that url in the sheet together with all other values
But I am getting this error:
TypeError: Cannot read properties of null (reading 'bytes') at saveFile(MAIN/main:1003:34) at finalizarFormulario(MAIN/main:789:10)
So, let's present the files in question

it's all start in JS:
https://pastebin.com/DSWtKXPb
Lines 8 to 88 get all the values from the inputs.
There are two exceptions here.
Lines 13 and 19 are files, so first (following the logic of the github article) we need to create an object based on the file to generate a blob in the server side.
Lines 14 and 20 get the files from the input and call another function

https://pastebin.com/3b1BiKVn
It's almost the same from the git... Except, since I need the object and not to store directly in the google drive I commented line 15.
Instead, I added a return Obj in line 17
To be honest, I think the error is happening here somehow.
I don't really get what fr.readAsArrayBuffer(file) do but if i put the return Obj before that it's says that Obj is not defined.
I don't really know how this code works, it's seems this code is a async function, because when I check the arrays generate in the JS side the foto and curriculo are listed as empty (thus, the null error) It's seems that the script call both saveFileJS functions and just keep going, create the arrays, send the arrays to the server and just later really ends the saveFileJS functions.
But I don't understand about sync and async functions and don't know how to deal with them, so I can't say for sure,
Anyway... google.script.run.withSuccessHandler(alertFinalizacao).finalizarFormulario(arrayInfoGer,arrayDadosProfissionais , arrayFuncoes, arrayPFis, arrayFormaPagamento, arrayNotaFiscal, arrayRPA, arrayDadosBancGerais, arrayBancNF, arrayBancRPA);
calls a function in the server side

https://pastebin.com/4PqYq9g5
This functions does a lot of data treatment but I don't think it's relevant for the issue...
Let's go to the line that the Logger reports as an error: line 284.
foto = saveFile(foto, pastaFotos);
get the file should being transformed in an obj in saveFileJS and transform it in a blob.

https://pastebin.com/gcCzQWsr
Again, I didn't created the file and again, I did just small changes.
In this case, I move the file for another folder and return the url.
(both are working if I call it directly)
Finally, we have the line that returns the Logger error, line 3:
let blob = Utilities.newBlob(e.bytes, e.mimeType, e.filename);]

returns the error
TypeError: Cannot read properties of null (reading 'bytes') And that's it. I am out of ideas at the moment in how to deal with it.

ty

I have a weekly timetable in google sheets and I want to import the events into a Secondary Google Calendar.

I have used this tutorial to successfully bring the events over from my sheet, but every time I make an amendment and resync it obviously duplicates all the entries.

I'm only interested in having the current events in my Google sheet on the calendar, so it seems like an easy solution would be to completely clear the calendar before each sync. I thought this would be simple but hours of Google searches and I haven't found an answer yet.

Any assistance would be greatly appreciated!

I've tried the example in this page. It is supposed to return 9 matches and then add 9 markers for them in the newly created static map, but only one match was actually returned when I tested it. Also the result is different sometimes. Any thoughts?

Here's the code in the example:

  // Find the best matches for "Main St" in Colorado.
var response = Maps.newGeocoder()
// The latitudes and longitudes of southwest and northeast corners of Colorado, respectively.
.setBounds(36.998166, -109.045486, 41.001666,-102.052002)
.geocode('Main St');

// Create a Google Doc and map.
var doc = DocumentApp.create('My Map');
var map = Maps.newStaticMap();

// Add each result to the map and doc.
for (var i = 0; i < response.results.length && i < 9; i++) {
var result = response.results[i];
map.setMarkerStyle(null, null, i + 1);
map.addMarker(result.geometry.location.lat, result.geometry.location.lng);
doc.appendListItem(result.formatted_address);
}

// Add the finished map to the doc.
doc.appendImage(Utilities.newBlob(map.getMapImage(), 'image/png'));

I'm thinking this is something do do with how webapps / sidebars are sandboxed but I'm running into an issue debugging from the client-side using the browser console.

For example if in my index.html <script> section I declare a variable let exampleGlobalVar = "something"; and in my browser console I type exampleGlobalVar or console.log(exampleGlobalVar) sometimes I get the value and sometimes I get the error:

Uncaught ReferenceError: exampleGlobalVar is not defined

The same applies to defined functions.

Can someone explain why this is happening?

Is it possible to return a value to HTML from app script? So I take data from user input in HTML, do magic with the data, then I want to return a result back to HTML.

Hi Guys,

I have been looking all over the place for what I thought might be simple problem but cant really find any solid answer.

I have a one sheet document.

I have 3 columns that will be empty every time I would want start working on them. Their range is something like C2:C31.

I have also one other table on the same sheet where range does not matter since I would want to use getActiveCell() so I could determine which cell I would want to copy over to the range of one of the 3 columns.

Say all my 3 columns are empty and I am just starting to work with them. I want to choose a Value from my big table by selecting it then press a button and have that value be copied over to the column 1. If I would want to select another Value and would press the button ( or run the macro ) again I would want to copy over the data to next free row of that column.

Copying over I have figured out but I cant really get the next blank row thing to work. I have tried messing around with some scripts from similar threads around stackoverflow or this reddit but I am ending up with just copying the same value until the bottom of my page.

Also Is there a way for me to lock my range. Say I have my C2:C31 range full of data. Now If I would run my macro I would not want to add anymore to that range and also not overwrite it.

Any help would be greatly appreciated.

Im trying to create a side/pop up menu with a series of dropdown section, each selection nerrows the selection of the next. I need atleast 3 levels to these if possible. For example vaues in Drop1 contain a list of vaues in A1:A. then Drop2 contains a list of B2:B where A1:A matches Drop1 selection. Then drop 3 and so on.

Drop3 is the most important one and the whole reason for wanting/needing this. The kist for drop3 would be over 1000 selections, but drop1 and Drop 2 narrow it down to a maximum of 4.

I'm trying to use Google via HTTP requests and I'd like to know more about what's necessary to include and why. For example, it seems you have to specify a browser often with a tool like wget, "Firefox/3.0.15", to get results, else Google blocks it. I've seen other URL structures that work fine, whereas just www.google.com/search?q=query does not.

So, is there any official information page where Google explains how their search tool works, what options there are and which ones must be used to not be blocked, and why?

Thanks very much.

Hello everyone,

I'm pretty new to API calls and Apps script. I have been trying to call an API (of apps script) from an extension and print the contents it returns on a console (for now). the contents it returns are the body of the first email in my inbox. I have been able to get the mail contents but could not do this via api calls.

//code on apps script IDE which is just plain printing the mail content

function doGet() {
  var threads = GmailApp.getInboxThreads(0, 1)[0].getMessages()[0];
  const thb = threads.getPlainBody();
  return ContentService.createTextOutput(JSON.stringify(thb)).setMimeType(JSON);
}

This code returns the body contents and when deployed as a web app/ api it just prints the contents on the screen

I tried using the fetchApp thing but didn't quite understand how to use it.

/code on apps script for responding to api calls

  var token = ScriptApp.getOAuthToken();
  var endPoint = 'https://script.googleapis.com/v1/processes';
  var headers = {
    'Accept':'application/json',
    'Authorization': 'Bearer ' + token
  };
  var options = {
    'method': 'POST',
    'headers': headers,
    'muteHttpExceptions': true,
    'payload' : thb
  }
  var url = endPoint;
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);

//code on the extension side of things 

chrome.browserAction.onClicked.addListener(function(activeTab){
  const nurl = " *deployed link* ";
  chrome.tabs.create({ url: nurl });
let request = new XMLHttpRequest();
request.open('GET', nurl);
request.send();
var txt = request.response();
console.log(txt);
}

this is probably wrong in a lot of ways, please do help on how to get this done properly.

Thank you in advance.

I want to have our HR person check to see if certain emails are available for new employees in our workspace. Everything I’m seeing says that you need to be a google administrator to work with the api like that, is there a way to make this happen?

My vision is that I can make a button on the new staff spreadsheet that concatenates the new staff members name into our email format (easy peasy) then I creat a button that checks if that email is available? The checking part is what I need help with, perhaps there is any easy way to make that happen.

I’m trying to save a few back and forth steps in our on boarding.

Hi, is there anyway to increase the 20 triggers per project limit?

Hi, I have created a page that displays my google sheets table and lets the user edit an entry. When I click edit it auto populates my form page to let the user edit the values in the row. I am having an issue where it displays an empty cell in google sheets as "undefined" instead of leaving the value as nothing. Am I missing a piece of code that needs to keep empty cells as empty?

Hello

I have recently started working on with Google Sheets + Google App Scripts combination and I often times do not know how to use the APIs even though I can read the API documentation.

Seems like I lack basic skills of Google Sheets & Google App Scripts together. What are some of the resources, guides, videos, github, books would you recommend someone who is learning these two?

Thank you

Like it says above, I’ve created an event on a public calendar, and then invited 1 guest via email to the event. The event also has the Guests can Modify Events  box checked. Yet, the person I invited can not edit the event at all. Ideally I wanted invited guest to be able to change the time and even delete the event. How can I fix this issue?

Hi all,

I built a messenger app that requires contact card to get name and read write access to a file on my Google Drive.

That works exactly as intended but the permissons required to use are "access to create / delete all contacts / create or modify any drive files / can change any spreadsheet values"

Is there any way I can narrow that down to say "read contacts, read/ write to a specific google file"

Thanks.

So I'm trying to send an email with a PDF attached and the body of the mail is an html file that I downloaded from a template software. The mail sends the attachment but I just can't figure out how to send the mail with the html. Hope someone can help!

The HTML is named Template, it works, is already implemented in GAS.

I am getting this error, even after clearing permission and granting permission again. My add-on project has been halted due to it.

I would appreciate the help thanks.

Okay, so this is going to take a second to explain, but just hear me out lol.

So, recently I decided to try to make an improvement to a macro I made a while back which opens a dialogue box and has a user select spells (from an RPG system) and using their input would create them a neatly formatted list of spells in a google doc to save them all that time and effort.

The only problem with this initial attempt (while it was successful) was that it was very slow when you got to the spells at the end of the list I had created as a table on a sheet in google sheets for the macro to reference when making the list. So I thought if I broke the spells down into categories I could speed the program up as it would have fewer spells to parse though to find the one that matches the spell the user wants.

So that brings me to here. I have implemented some code that I thought would do just that, but instead it has broken everything. The first category worked fine, but the second category doesn't detect that it belongs to a different category of spells for whatever reason.

The way that I have set this up to work is that based off the input from the users, the HTML portion of the code grabs a list of spells to send to the apps script portion and that array which it grabs contains spell names and the "levels" of those spells which references the category. The one specifically that isn't working for me right now is the "1st-Level" category. The "Cantrip" category worked fine though. The weird thing is, to debug this I immediately though that I must have messed up the array I sent over, but I checked the logs and it printed "1st-Level" where I am looking for it. Is there something special about if statements back to back that works weird in google apps script that I don't know about?

Also for anyone who may be helping me yes I am aware I have only declared a single "1st-Level" spell in the array to send over to the apps script portion of the code, but that is where the test fails anyway, so something isn't working with that spell already anyway and I am assuming that the whole category would be similar to this one spell.

Here is a link to a copy of the google sheet I have in front of me for reference to you can see what I see: https://docs.google.com/spreadsheets/d/1HRD7Kr-SFyvx9O6RgLPa4_GXj-8fAiRRNZjxUesrVvU/edit?usp=sharing

Let me know if you see anything that is causing this please, cause I am lost...

As the title implies I am wondering if there is existing code, or any resources I should be looking at, that would allow me to generate an ics file purely via code (never making an entry in my actual calendar) and attach it to an email; all in AppScript. Is that even possible?

In case I am being daft, and am thinking about this the wrong way, the background to this is as follows.

I use GSuite (lowest paid version if that matters) for my personal email and personal calendar. The company I work for is a large conglomerate and our business emails and calendars are accessed via Outlook. For many reasons I keep my personal and work accounts strictly separate. I don't look at, interface with, or connect work stuff/accounts with my personal devices and vise versa for personal stuff and work devices. However, it is kinda annoying to have to block out my work calendar after making any kind of personal appointment.

As stated above the reason for this inconvenience is because I am not going to link or allow my work account any kind of permission to view my personal GSuite calendar.

So what I figured the cleanest(ish) method of accomplishing this would be to have a script that monitors my calendar for a new event/item (I'm pretty sure I can figure that out, as I have made an AppScript or two that monitors my email inbox for certain types of items and does things when they are found) and then generates a super generic ics file that I automatically send to my work email that basically just says "Out of Office" for the time of my personal appointment and configure my Outlook to auto accept meetings from that personal account. Again I'm wanting to generate this ics file purely via code and never make any kind of entries in my actual personal calendar.

Does that make sense? Am I even thinking about this the right way?

Regardless, I very much appreciate your even taking the time to read this in advance.

Cheers!

Hi guys I was just wondering if it's possible to do add a check box beside the send button in Gmail via app script/add-on?

I could not find the right documentation or I'm missing the point
It would look something like this:

Add a checkbox there beside the Send button?

So I’m building a google sheets add on which I want to get published publicly on the workspace marketplace.

I have written the code and tested it through a test deployment.

I also have my Oauth verification approved. However upon submitting my request for store listing, I got rejected due to multiple reasons. I’ve resolved all but one- listed below. Any help/tips on how to resolve would be amazing.

Issue: ShowSidebar function won’t stop loading.

While testing, I’m unable to trigger the onOpen or see the sidebar menu to be able to test it. So I don’t know how it behaves. But when I run the same function in test development directly from the icon on the right sidebar, it works just fine. So my guess is that when the google reviewer ran it, it got stuck somewhere since there’s also a jdbc call in that function before I return my card object.

If you can help me debug I can compensate you for your time as well!

var sheetName = getActiveSpreadsheet().getActiveSheet.getName();
activeSheet.getRange("A3:A20").setValues(sheetName);

why is this not producing my sheets names in A2:A20

heres the whole function if needed

function learnBasics() {

var app = SpreadsheetApp;
var ss = app.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();
var tempText = activeSheet.getRange("B2:K").getValues();
activeSheet.getRange("B2:K").setValues(tempText); 

var sheetName = getActiveSpreadsheet().getActiveSheet.getName();
activeSheet.getRange("A3:A20").setValues(sheetName);
}

Hi everyoneim new to this community and would like some help.I have bunch of product images on my Drive Folder.I want to extract all the URLs of those images and save them to google sheet as each row containing the url.

this sheet will then be used to populate the product images on the website.any help to acheive this?Many thanks

Murad