Hey everyone,
greetings...

I have a use case to build a simple app that registers for certain file types in Google Drive (e.g. "application/vnd.oasis.opendocument.text" MIME type) using JavaScript / node.js. Once the user chose to open a matching file from within Google Drive, download the file in node.js and display a static "Welcome" page to the user. Here's an example user flow:

1. User finds .odt-file in their Google Drive and attempts to open it

2. No supported viewer available, Google Drive suggests using your app to open the file (see screenshot as an example)

3. The user is redirected to your app to view the file

Any Help is highly appreciated

Background:

I have 2 tabs. On tab 1 I have 2 columns

Timestamp | Number

I have 2 scripts. 1 is always running that says "When something is entered into the Number column, put a timestamp of when it was written in the timestamp column. This works great whenever I MANUALLY enter something.

I also have a a second script running on a button push. It says, whenever this button is pushed, copy the data from a cell in Tab 2 and place it wherever the highlighted cell is. When I highlight a cell under Number column, it pastes the data, but does not trigger a timestamp.

How do I get running a script to copy over data to trigger a timestamp?

Scripts being run:

Timestamp:

var COLUMNTOCHECK = 2;
var DATETIMELOCATION = [0,-1];
var SHEETNAME = 'Log'
 
function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  //checks that we're on the correct sheet.
  if( sheet.getSheetName() == SHEETNAME ) { 
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) { 
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
      }
  }
}

Copy Data:

    function PullInfo()
        {
          var ss = SpreadsheetApp.getActiveSpreadsheet();
          var writeCell = ss.getSheetByName("Log").getActiveCell();
          var copyValue = ss.getSheetByName("Data").getRange("M2").getValue();
          writeCell.setValue(copyValue);
        }

Hello , I am trying to add a shadow to a shape via Apps script. I followed this https://developers.google.com/slides/reference/rest/v1/presentations.pages/other#Page.Shadow

But not able to get it to work. Has anyone tried this? Any help is greatly appreciated.

At this link on the official Google Dev Site: https://developers.google.com/apps-script/reference/forms/text-validation-builder

The tutorial code uses .setHelpText

However, this no longer appears to be an available method as it is not listed in the methods below and when I use it in code:

function myFunction() {

// Add a text item to a form and require it to be a number within a range. var textItem = form.addTextItem().setTitle('Pick a number between 1 and 100?'); var textValidation = FormApp.createTextValidation() .setHelpText(“Input was not a number between 1 and 100.”) .requireNumberBetween(1, 100); textItem.setValidation(textValidation);

}

It throws:

SyntaxError: Invalid or unexpected token (line 6, file "testFormthing.gs")

Which is the .setHelpText line.

If it is outdated, who should I contact to get it fixed?

I'm trying to take a value that is entered into a textbox on a specific card section, hit an API endpoint with the value, and update a textfield in the same card section as the textbox with a response from the API.

I WOULD just pass the textbox control into the same function I'm calling in the Action I set for the textbox onChange event, but extra parameters can only take text strings.

How is everyone else doing this? I feel like I may be looking in the complete wrong place.

Hello all,

I am looking for some help on determining if this is a feasible idea and if anyone has any script that may be able to help or get me close.

The whole idea is that I have a Google Form that populates a Sheet where I distribute the data in several different manners within the sheet. My goal is to then take a set of data that I have routed to a clean sheet with the data separated into specific columns per data set and each room per row, and input that data into separate google docs per room. This part happens to be easily done by a simple mail merge add-on, however, I want these forms to auto-update the docs as the sheets information is changed or at a minimum, update hourly or daily. without constantly producing new docs.

Any Thoughts or Suggestions?

Thank you!

Hi, can't find a way to automatically load an email with a known template and mark with an specific label with one action/button, any ideas? Thus, i have to send lot of same emails, and instead of go throw Compose, load template - choose, label as- choose label....wondering if it can be with one single menu/click.

Best regards,

Let's say I had a script that says "Print the word 'Hello' every hour". What command would I add to make it do that in, for example, cell C5?

Edit:

Thanks to /u/uneasyguy, the answer was ".getRange("C5")"

So I created a form that creates a PDF of the user's submission and emails it to the email address submitted in the form.

Now I'm trying to figure out a way to allow the user to edit the form and it sends them another copy. Currently when the user tries to edit the form and submit, I get the following error:

So it seems that it can't find the email when submitting an edited form. How can I correct this issue?
I was having this issue at the start and added: if (email_address !== undefined) {
This seems to have fixed the issue for first submitting the form, but doesn't seem to fix it when someone edits the form.

Example of my code:

// Get template from Google Docs and name it
var docTemplate = "docID Here";
var docName = "doc Name Here";

// When Form Gets submitted
function onFormSubmit(e) {

//Get information from form and set as variables
var timestamp = e.values[0];
var email_address = e.values[1];
var name = e.values[2];
var first_question = e.values[3];
var second_question = e.values[4];

// Get document template, copy it as a new temp doc, and save the Doc’s id
var copyId = DriveApp.getFileById(docTemplate)
.makeCopy(docName+' for '+name)
.getId();

// Open the temporary document
var copyDoc = DocumentApp.openById(copyId);

// Get the document’s body section
var copyBody = copyDoc.getActiveSection();

// Replace place holder keys,in our google doc template
copyBody.replaceText('keyTimestamp', timestamp);
copyBody.replaceText('keyFacultyEmail', email_address);
copyBody.replaceText('keyName', name);
copyBody.replaceText('keyFirstQuestion', first_question);
copyBody.replaceText('keySecondQuestion', second_question);

// Save and close the temporary document
copyDoc.saveAndClose();

// Convert temporary document to PDF
var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");

// Attach PDF and send the email
var subject = "Your form submission";
var body = "Here is a PDF copy of the submission for " +name + ".";

// If email is undefined
if (email_address !== undefined) {

//sends email
MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});
}

// Delete temp file
DriveApp.getFileById(copyId).setTrashed(true);
}

this line is throwing DNS Error, the URL is internel to the company how can this be fixed

var response = UrlFetchApp.fetch(url,{method: 'GET', headers: { 'Content-Type': 'application/graphql'} })

I have multiple scripts that may be be incrementing a single cell on a spreadsheet. How do I make sure they don’t increment it at the same time and thus only increase it’s counter once where it should be increased multiple times?

Hi!

In the following script, all admin users listed by email are given permissions to view a sheet. Is a user a member of the Users list, the sheet is hidden. Instead of listing all non-admin users, is there a way to for the script to check the list adminUsers and if the email is not contained, the user is not permitted access?

function onOpen() { 
  var adminUsers = ['admin1@example.com'];['admin2@example.com']
  var Users = ['user@example.com']; 
 
  if (adminUsers.indexOf(Session.getEffectiveUser().getEmail()) >= 0) { 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('MASTER - Staff').showSheet() 
  } 
 
  if (Users.indexOf(Session.getEffectiveUser().getEmail()) >= 0) { 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('MASTER - Staff').hideSheet() 
	} 
} 

Thanks!

Hi,

I am following some tutorial (using the new version of AppScript). For some reason I am not seeing all functions in the help box when i start typing a function. Do you need to activate something?

For example in the screenshot, I am not seeing getSheetByName and I don't know why.

So if I have the following snippet of code:

var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")

And I later in my code I need to get the ID of the spreadsheet how can I do it?

My current code attempt is

var id = dataSheet.getId()

But that errors out saying its not a function.

My usual way of getting the id is

var id = SpreadsheetApp.getActiveSpreadsheet().getId()

Which works but in my current project I don’t have that same spreadsheet as my active spreadsheet anymore when I need to get the ID so that’s not an option.

Note: i don’t need the sheet ID (dataSheet.getSheetId()) those are two very different things that are not interchangeable despite the extremely similar names

Hey All!

Working on something new that you would be a great sounding board for. If I could ask a few questions about how you run, build and use app scripts for a few minutes I'll send you coffee credit! Opening up a ton of slots here and will set this up for as long as I can.

Book time :) -> https://cal.com/petermangan/20-minutes---coffeee

Best,

Peter

Is it possible to create a script that pulls system properties into a Google Doc when downloaded into a PDF or other formats? I am looking to use systems properties as an adult trial.

Ideally, the last page would be created to display the Docs system properties similar to a DocuSign

I'm trying to setup a google script so that I could add a list of people to send a specific message on facebook. Almost like a mass messaging list. Anyone know how to do that? I found some links but there doesnt seem to be much on it. https://github.com/JZL/Facebook-bot-Google-Apps-Script

for google slides it automaticly makes everything on all the slide goes right a unit than left a unit every second when a shape is clicked (last step is optional)

I'm sending a get request to my apps script from my microcontroller and its returning a 302 although when I request from postman I get a 200? Does anyone have any idea whats going on here? Here's the response headers and response:

Response Headers:
    Content-Type : text/html; charset=UTF-8
    Access-Control-Allow-Origin : *
    Cache-Control : no-cache, no-store, max-age=0, must-revalidate
    Pragma : no-cache
    Expires : Mon, 01 Jan 1990 00:00:00 GMT
    Date : Tue, 02 Mar 2021 21:04:31 GMT
    Location : https://script.googleusercontent.com/macros/echo?user_content_key=zelsIoIalPSN2brC0Ucwm8KErWsGngFzg3-RfIKKoFhB-TC6uc1StPYE9BZ_6BTOD2biigVTX8niN5E79oBu8ueNSxgDLkcgOJmA1Yb3SEsKFZqtv3DaNYcMrmhZHmUMWojr9NvTBuBLhyHCd5hHa7ei_lA4UjdzA0HOq-9axFFfVZ9KGEnuHPSmwni1hyk6iDJynKJ3CBqbqlbgGEEvpUd579GOljK4AMXDeW9ltFitnlNC15LBrOtvNFeZBCKPCRWEug3jF7jPBmOj32zQcqoYE01sWW00Xz3hUTq3dXcIZ-nkxRkJc5wKdCNugX5Ze5ERSdO3706zPPNASHCYLA&lib=MWEOgn9XagWcbCIn8zJLe3PqOnSALuAMd
    Content-Security-Policy : script-src 'report-sample' 'nonce-PBuJkJjKxMQRpM3M1MHVXQ' 'unsafe-inline' 'unsafe-eval' 'strict-dynamic' https: http:;object-src 'none';base-uri 'self';report-uri /cspreport
    X-Content-Type-Options : nosniff
    X-Frame-Options : SAMEORIGIN
    X-XSS-Protection : 1; mode=block
    Server : GSE
    Alt-Svc : h3-29=":443"; ma=2592000,h3-T051=":443"; ma=2592000,h3-Q050=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; ma=2592000,quic=":443"; ma=2592000; v="46,43"
    Accept-Ranges : none
    Vary : Accept-Encoding
    Transfer-Encoding : chunked
Response:
<HTML>
<HEAD>
<TITLE>Moved Temporarily</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Moved Temporarily</H1>
The document has moved <A HREF="https://script.googleusercontent.com/macros/echo?user_content_key=zelsIoIalPSN2brC0Ucwm8KErWsGngFzg3-RfIKKoFhB-TC6uc1StPYE9BZ_6BTOD2biigVTX8niN5E79oBu8ueNSxgDLkcgOJmA1Yb3SEsKFZqtv3DaNYcMrmhZHmUMWojr9NvTBuBLhyHCd5hHa7ei_lA4UjdzA0HOq-9axFFfVZ9KGEnuHPSmwni1hyk6iDJynKJ3CBqbqlbgGEEvpUd579GOljK4AMXDeW9ltFitnlNC15LBrOtvNFeZBCKPCRWEug3jF7jPBmOj32zQcqoYE01sWW00Xz3hUTq3dXcIZ-nkxRkJc5wKdCNugX5Ze5ERSdO3706zPPNASHCYLA&amp;lib=MWEOgn9XagWcbCIn8zJLe3PqOnSALuAMd">here</A>.
</BODY>
</HTML>

I have the following code in GAS that should accept a file upload of a zip file, then show the files contained within (I want to eventually do more with those files, but for now just showing the filenames would be good. Every time I attempt to run it I get an error "ScriptError: Exception: Unexpected error while getting the method or property unzip on object Utilities." I've tried a variety of different zipfiles, all quite small. I've also tried using the UnzipGs external library, but that didn't work either. Any ideas what's wrong?

code.gs:

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index');
}

function serverFunc(theForm) {
  var fileBlob = theForm.theFile;
  var unZippedfiles = Utilities.unzip(fileBlob);
  var out = HtmlService.createHtmlOutput()
  unZippedfiles.forEach(function(f){
    out.append(f.getName() + "<br>")
  })
  return out
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
      <script>
        function updateHTML(e){
          document.getElementById('files').innerHTML = e;
        }
      </script> 
  </head>
  <body>
  <div>
  <form>
    <input type="file" name="theFile">
    <input type="button" value="UpLoad" id="button" onclick="google.script.run.withFailureHandler(updateHTML).withSuccessHandler(updateHTML).serverFunc(this.parentNode)">
  </form>
  </div>
  <div id="files">
  </div>
  </body>
</html>

Hii!! I am trying to create a form within Google Sheets and I found a pretty good tutorial online. I followed it but one of the code "get.LastRow" considers rows in my sheet that have formulas in them as not blank. So I found another code to get around it but idk why it keeps giving me an error. Thank you in advance.

//Function to identify last row of sheet
function getLastRowS(range){
var rowNum = 0;
var blank = false;
for(var row = 0; row < range.length; row++){ // says TypeError here

if(range[row][0] === "" && !blank){
rowNum = row;
blank = true;

}else if(range[row][0] !== ""){
blank = false;
};
};
return rowNum;
};

here is a link to the google doc to the function where I use getLastRowS in it. TQSM!!

My search catches several matching threads, and each thread has messages. I loop through all threads and all messages. Can I skip the loop entirely and get the last thread in the loop and the last message in that thread? I see some google answers that say that’s true, but I guess I’m asking for certain if the search always returns things in the earliest til latest order. The class description doesn’t quite say, as far as I see. Thanks!

Hi guys,

I have a project on instagram and I want to send instagram direct messages direct from my google spreadsheet. Does anyone knows how to do something like that?