tag:blogger.com,1999:blog-3979648284880167220.post2155735124889855571..comments2024-03-07T01:46:22.570-08:00Comments on tech cogitation: Folders as a workaround for Google form attachmentsJameshttp://www.blogger.com/profile/08512693901366641948noreply@blogger.comBlogger93125tag:blogger.com,1999:blog-3979648284880167220.post-2356470613097469032017-09-12T10:25:45.394-07:002017-09-12T10:25:45.394-07:00Hi Bolbo, you need to take a copy of the sheet, yo...Hi Bolbo, you need to take a copy of the sheet, you can then use the script editor and change the form edit url section to the URL of the form that will be generated with the copy, and change the google drive folder ID, and it should be good to goJameshttps://www.blogger.com/profile/08512693901366641948noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-88126768205643211322017-09-12T08:59:31.669-07:002017-09-12T08:59:31.669-07:00It does not work for me.. I cannot open the script...It does not work for me.. I cannot open the script editor (the sheet is on view mode) perhaps the code link you provided is not the same as the one within the spreadheet?Bolbohttps://www.blogger.com/profile/09696438826367321981noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-50189044708616176432017-09-12T03:38:00.820-07:002017-09-12T03:38:00.820-07:00Hi Bolbo - I have made a copy of the demo from 17t...Hi Bolbo - I have made a copy of the demo from 17th March and it did generate a timestamped subfolder OK here. All I needed to change in the code was the URL of the form (these days the code needs the edit URL rather than the view URL to work) The code created me a folder under my name, and when I next did this, the code created a subfolder under that, with the timestamp as the name.Jameshttps://www.blogger.com/profile/08512693901366641948noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-70075269150852916102017-09-11T08:24:34.103-07:002017-09-11T08:24:34.103-07:00Thanks! I think I had not used the edit form link ...Thanks! I think I had not used the edit form link but the view one. Now buttons on the AT are visible.<br /><br />Please, can you see why your demo from 17 march 2016 does not generate time-stamped subfolders for existing users?Bolbohttps://www.blogger.com/profile/09696438826367321981noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-74937492258330679422017-09-04T07:04:48.205-07:002017-09-04T07:04:48.205-07:00Hi Bolbo, thanks - demos seem to be fine. here'...Hi Bolbo, thanks - demos seem to be fine. here's a quick AT based of the first https://awesome-table.com/-KtCBJ1jIMF1mkxJHfPk/view - (NB there is dummy data here, so the links to edit and folder dont go to a valid ID) If your buttons are not displaying, ensure you have have referenced the template - e.g Template!A1:C2 in the field Template range in the awesome table setup. Jameshttps://www.blogger.com/profile/08512693901366641948noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-15927831973318644542017-09-03T09:31:29.242-07:002017-09-03T09:31:29.242-07:00Hi James. Could you check if the demos are still w...Hi James. Could you check if the demos are still working properly?<br />Links (column H) are not generated.<br />Buttons are nor displayed on the AT.<br />The second demo does not generate subfolders for submissions with the same user.<br />Bolbohttps://www.blogger.com/profile/09696438826367321981noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-50790418939331386212017-03-10T01:23:04.985-08:002017-03-10T01:23:04.985-08:00A pleasure :-)A pleasure :-)Jameshttps://www.blogger.com/profile/08512693901366641948noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-81338656358502157352017-03-10T01:09:39.773-08:002017-03-10T01:09:39.773-08:00Also thank you for the link to the basic guide to ...Also thank you for the link to the basic guide to scripting. :DJMShttps://www.blogger.com/profile/01537002261604229843noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-75252648088491283802017-03-10T01:09:00.368-08:002017-03-10T01:09:00.368-08:00O gosh, that was a very quick response considering...O gosh, that was a very quick response considering the last comment was way back October 2016.hahaha Thank you so much! JMShttps://www.blogger.com/profile/01537002261604229843noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-84555279357271582412017-03-10T00:26:49.560-08:002017-03-10T00:26:49.560-08:00Hi - the script goes into the Google sheet. If yo...Hi - the script goes into the Google sheet. If you go into the demo sheet linked in the blog post (https://docs.google.com/spreadsheets/d/1IQu3apOHUS3ZyD33xEvuKrMFRqfCL67DW9hym8SSLLQ/edit#gid=1454573290) then go to tools-> script editor you can see the script used. For a basic guide to adding scripts to sheets, here's some information from Google https://developers.google.com/apps-script/guides/sheets Hope this helpsJameshttps://www.blogger.com/profile/08512693901366641948noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-29025693224578608192017-03-10T00:22:24.140-08:002017-03-10T00:22:24.140-08:00I am a very comfortable with spreadsheets myself b...I am a very comfortable with spreadsheets myself but the script part doesn't make any sense to me. For one, your instructions began with: "To begin with, I created a Google form, and a Google sheet to collect the information." Which I also have, and my form is a little lengthy. Now I'm looking for the second step as to how or where to place the script. But I don't think there is any part in this blog page where you talk about step two. You just immediately began with all the scripts that you used. I'm sure this is very helpful to others, which I have noted in the comment section. But can you please make it helpful to someone like me who doesn't know where step two goes? Thank you so much.JMShttps://www.blogger.com/profile/01537002261604229843noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-76832919907657602432016-10-25T04:59:59.553-07:002016-10-25T04:59:59.553-07:00i have a new error after fixing the last error her...i have a new error after fixing the last error here is the new one <br /><br />Invalid argument: permission.value (line 56, file "Code")Anonymoushttps://www.blogger.com/profile/04944023757625433308noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-80604952026171265812016-10-24T14:23:18.887-07:002016-10-24T14:23:18.887-07:00any feedback James any feedback James Anonymoushttps://www.blogger.com/profile/04944023757625433308noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-54306251724624203662016-10-20T19:23:59.463-07:002016-10-20T19:23:59.463-07:00yes its correct and the name is (Event Registratio...yes its correct and the name is (Event Registration) and here is the code<br /> <br /><br /><br /> {<br /> <br /> <br /> //This part of the code is what James posted on his blog<br />var formURL = 'https://docs.google.com/forms/d/1IuvZAImVIJOej0JcL0PQsHVHkmqkayHCUZdBLCoHcHo/edit'; // enter the url for the form<br />var sheetName = 'Event Registration'; // enter the name of the actual Google Sheet you are working in<br />var columnIndex = 12; //the number here is a count with A = 1, it indicates where the link should go<br /> <br />// script to get the edit link<br /> <br />function getEditResponseUrls(){<br />var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Event Registration"); //name of the sheet within the spreadsheet<br />var data = sheet.getDataRange().getValues();<br />var form = FormApp.openByUrl(formURL);<br />for(var i = 2; i < data.length; i++) {<br /> if(data[i][0] != '' && (data[i][columnIndex-1] == '' || !data[i][columnIndex-1])) {<br /> var timestamp = data[i][0];<br /> var formSubmitted = form.getResponses(timestamp);<br /> if(formSubmitted.length < 1) continue;<br /> var editResponseUrl = formSubmitted[0].getEditResponseUrl();<br /> }<br /> }<br />}<br /> <br />// script to create a folder for document uploads<br /> <br />function createFolder () {<br /> <br />//var FOLDER_CREATED = "FOLDER_CREATED";<br /> <br /> // Get current spreadsheet<br /> var ss = SpreadsheetApp.getActiveSpreadsheet();<br /> var sheet = ss.getSheetByName("Event Registration"); //name of the sheet within the spreadsheet<br /> var data = ss.getDataRange() // Get all non-blank cells<br /> .getValues() // Get array of values<br /> .splice(2); // Remove header lines<br /> <br />// Get the ID of the root folder, where the sub folders will be created<br /> var DriveFolder = DriveApp.getFolderById("0Bzcjq6yw1tARUm52MW5IMFZsVjA");<br /> <br /><br />//This is where the code differentiates from James' post<br />//Add items to the row<br />var i= data.length +1 ;//find the last item in the row<br />sheet.getRange(i+1, 10).setValue(i+"i"); //set the J column to the value of i for debugging. Can be changed to another appropriate column number by changing the number 10<br />var row = data[i]; //set the current item to a variable called row<br /><br />var folderName = sheet.getRange(i+1,2).getValue() ; //name from column B<br />sheet.getRange(i+1, 11).setValue(folderName+i);//test the folder name<br />i++<br />if (sheet.getRange(i,12).getValue() == "") { //the value of 12 means column L, so check column L for a URL, L may be changed to any suitable column number<br />//Get the email address<br />var emailAddress = sheet.getRange(i,3).getValue() ; // Column C, which is where the spreadsheet would hold the email address. Can be changed to the suitable column number where the correct info is located<br />//create the folder<br /> var folderpath = DriveFolder.createFolder(zizo).addEditor(emailAddress).getId(); <br />//get the path to the folder<br />var pathtoemail = "https://drive.google.com/drive/folders/"+folderpath;<br />sheet.getRange(i, 12).setValue(pathtoemail);//show the folder path. The value of 12 means column L, this can be changed, but should be a direct match with the value of the column for the IF statement above.<br />}else {};<br />//Create the message and subject of the email <br />var message = 'Thank you for submitting. Here is the link to your drive folder. Please use this folder to upload and receive related documentation. ' +pathtoemail ; //a custom message, feel free to change anything inside the quotes <br />var subject = "Form Related Documentation." ; <br />//send the email<br /> MailApp.sendEmail(emailAddress, subject, message, {<br /> // cc: 'email@email.com' // optional cc<br /> <br /> });<br /> <br />}<br /> <br /> <br />}<br /><br /> <br />Anonymoushttps://www.blogger.com/profile/04944023757625433308noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-3648145284566513562016-10-20T07:27:31.835-07:002016-10-20T07:27:31.835-07:00Hi motaz - check to see if your sheet name is corr...Hi motaz - check to see if your sheet name is correctly defined. If it is, its a bit difficult to see exactly where the error is occurring. Can you share your sheet perhaps? Jameshttps://www.blogger.com/profile/08512693901366641948noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-30203394356388527082016-10-20T05:37:29.502-07:002016-10-20T05:37:29.502-07:00Hi James,
Reference to your article ( Folders as...Hi James, <br /><br />Reference to your article ( Folders as a workaround for Google form attachments) would you please support me as after i followed the mentioned steps and run the script , i got the below errors <br /><br />TypeError: Cannot call method "getDataRange" of null. (line 13, file "Code") <br /><br />and <br /><br />TypeError: Cannot call method "getRange" of null. (line 45, file "Code") <br /><br />however i followed your steps carefully <br /><br />Thanks for your support and great article ,also sorry for any inconvenience.Anonymoushttps://www.blogger.com/profile/04944023757625433308noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-68695426261573625802016-10-20T05:34:53.420-07:002016-10-20T05:34:53.420-07:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/04944023757625433308noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-5398270549741973562016-10-13T02:40:12.676-07:002016-10-13T02:40:12.676-07:00A pleasure :-) Glad all is working wellA pleasure :-) Glad all is working wellJameshttps://www.blogger.com/profile/08512693901366641948noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-919305030600498932016-10-13T01:43:51.958-07:002016-10-13T01:43:51.958-07:00You are awesome James!!!
I could never image goog...You are awesome James!!!<br /><br />I could never image google form with upload button can be created!!!<br /><br />I followed your suggestion and all sorted now. Able to run the script using form edit link and also able to bring response to row 3rd.<br /><br />Thanks again for your help :)Anonymoushttps://www.blogger.com/profile/11463955541010087354noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-74471606547745052322016-10-13T01:43:33.639-07:002016-10-13T01:43:33.639-07:00You are awesome James!!!
I could never image goog...You are awesome James!!!<br /><br />I could never image google form with upload button can be created!!!<br /><br />I followed your suggestion and all sorted now. Able to run the script using form edit link and also able to bring response to row 3rd.<br /><br />Thanks again for your help :)Anonymoushttps://www.blogger.com/profile/11463955541010087354noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-51093833515187795342016-10-13T00:53:33.196-07:002016-10-13T00:53:33.196-07:00Hi Kathar - I have updated the script - I think si...Hi Kathar - I have updated the script - I think since Google brought in new forms, any forms you create using the new version will need you to use the edit link rather than the view link in your script. I have tested here with the revised code and it works fine.<br /><br />For the responses, once you have a response, insert a line above, and put your filters in, any responses will then go from line 3 downwards and leave your filters on line two as desired.<br /><br />Hope this helps :-)<br />Jameshttps://www.blogger.com/profile/08512693901366641948noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-86656545947081596982016-10-13T00:22:33.398-07:002016-10-13T00:22:33.398-07:00Hi James - thanks for this article.
I have facing...Hi James - thanks for this article.<br /><br />I have facing two issues.<br /><br />1. function getEditResponseUrls() is not working and is giving below error.<br /><br />No item with the given ID could be found, or you do not have permission to access it. (line 10, file "Code")<br /><br />I have created form and just clicked on view button to view it and provided that link for formURL parameter in your script.<br /><br />2. Secon issue i am facing is that whenever any response is added using form, it is getting inserted in my worksheet's second row whereas it shall get inserted into 3 row because i have filter in my second row.<br /> Anonymoushttps://www.blogger.com/profile/11463955541010087354noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-87436746664769956322016-08-31T22:07:53.952-07:002016-08-31T22:07:53.952-07:00ah, i fixed it, sort of.
I got rid of this sectio...ah, i fixed it, sort of.<br /><br />I got rid of this section:<br /><br />for (var i=0; i<data.length; i++){<br /><br />var startRow = 2; // First row of data to process <br />var row = data[i];<br />var class = data[i][CLASS];<br />var folderName = class;<br /><br />and replaced it with <br /><br />var folderName = data.getRange(last,9,1,1).getValue()<br /><br />So my script no longer uses the for loop, which I think may be an issue if ever my form data gets out of sync with the google sheet. Not an ideal workaround, but it does what I need for now.Anonymoushttps://www.blogger.com/profile/02499654532262511433noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-68630317430159358212016-08-28T20:29:11.104-07:002016-08-28T20:29:11.104-07:00Hi James,
I've tried to modify your code slig...Hi James,<br /><br />I've tried to modify your code slightly, but for some strange reason, the folders created in my Google Drive are all named "undefined".<br /><br />The main modifications are as follows:<br />- the arrayformula was causing an error whereby my data.length defaulted to 1000, so I found a workaround using sheet.getRange.setFormula to define my unique ID instead,<br />- I used a copyTo function with {contentsOnly:true} to ensure that the folder name is a text string and not read as the function.<br /><br />I wonder if the error is because my unique ID is set up to have spaces in it.<br /><br />Code below; sorry it's a little messy, as I was trying to set up different sections as individual functions to test them separately.<br /><br />var formURL = "https://docs.google.com/forms/d/e/abcdefg/viewform"; // enter the url for the form<br />var sheetName = 'Form responses 1'; // enter the name of the sheet<br />var columnIndex = 9; //the number here is a count with A = 1, it indicates where the link should go<br /> <br />// script to check data length<br /><br />function test () {<br /> var ss = SpreadsheetApp.getActiveSpreadsheet();<br /> var sheet = ss.getSheetByName("Form responses 1");<br /> var data = ss.getDataRange() // Get all non-blank cells<br /> .getValues() // Get array of values<br /> Logger.log(data.length); <br />}<br /><br />// script to move values only<br /><br />function moveValuesOnly() {<br /> var ss = SpreadsheetApp.getActiveSpreadsheet();<br /> var sheet = ss.getSheetByName("Form responses 1");<br /> var range = sheet.getDataRange()<br /> var last = range.getLastRow()<br /> var ranges = sheet.getRange(last,8)<br /> ranges.copyTo(sheet.getRange(last,9),{contentsOnly:true})<br />}<br /><br />// script to create a folder for document uploads<br /><br />function createFolder () {<br /> <br />var FOLDER_CREATED = "FOLDER_CREATED";<br /> <br /> // Get current spreadsheet<br /> var ss = SpreadsheetApp.getActiveSpreadsheet();<br /> var sheet = ss.getSheetByName("Form responses 1");<br /> var data = ss.getDataRange() // Get all non-blank cells<br /> .getValues() // Get array of values<br /> .splice(1); // Remove header lines<br /> <br /> // Gets the last row in order to input the reference formula<br /> var range = sheet.getDataRange()<br /> var last = range.getLastRow()<br /><br /> // This puts the reference formula in column H<br /> sheet.getRange(last,8,1,1).setFormula('=((B1:B)&" "&(C1:C))')<br /><br /> // something to change it from a formula to a value<br /> moveValuesOnly()<br /> SpreadsheetApp.flush()<br /> <br /> // Define column number unique reference. Array starts at 0.<br /> var CLASS = 8; //column I<br /> <br />// Get the ID of the root folder, where the sub folders will be created<br /> var DriveFolder = DriveApp.getFolderById('abcdefg');<br /> <br /> // For each email address (row in a spreadsheet), create a folder,<br /> // name it with the data from the Class<br /><br /> for (var i=0; i<data.length; i++){<br /> <br /> var startRow = 2; // First row of data to process <br /> var row = data[i];<br /> var class = data[i][CLASS];<br /> var folderName = class;<br /> var folderstatus = row[9]; // column J<br /> <br /> if (folderstatus != FOLDER_CREATED) {<br /> <br /> //create the folder<br /> <br /> var folderpath = DriveFolder.createFolder(folderName).getUrl();<br /> <br />// send an email<br /> <br /> var emailAddress = row[6]; // Column G)<br /> var message = 'Thank you for submitting your Report - please upload the photos relating to this to the following folder ' +folderpath ; <br /> <br /> var subject = "Please upload photos"; <br /> MailApp.sendEmail(emailAddress, subject, message, {<br /> // optional cc<br /> <br />});<br /> <br /> //set the flag to avoid duplicates<br /> sheet.getRange(startRow + i, 10).setValue(FOLDER_CREATED);<br /> sheet.getRange(startRow + i, 11).setValue(folderpath);<br /> // Make sure the cell is updated right away <br /> SpreadsheetApp.flush();<br /> <br /> } <br /> <br /> }<br /> }<br />Anonymoushttps://www.blogger.com/profile/02499654532262511433noreply@blogger.comtag:blogger.com,1999:blog-3979648284880167220.post-73085959476944563162016-08-05T03:29:48.899-07:002016-08-05T03:29:48.899-07:00Hi - the sheet name should be the name of your she...Hi - the sheet name should be the name of your sheet (tab) rather than the spreadsheet name. Does this fix the problem for you?Jameshttps://www.blogger.com/profile/08512693901366641948noreply@blogger.com