Can I use a form field to rename upload pdf or doc using googlescript?

Question

I am working on a form that upload files with format pdf or doc. This form has other fields like class no. course name....etc. Can I use a form field To rename an upload File?... attached is the form draft

This is for uploading data from staff in my university. I have worked on previous suggestions but still need to change the name of the file upon the form field.enter image description here

score 1 · Answer 1 · answered Oct 08 '19 at 07:26

There is no dedicated form field for renaming an upload file, but you can create an additional field where you prompt the user to enter the name to which the file shall be renamed.

If a file is uploaded its URL on Google drive will be inserted into the corresponding field in the destination spreadsheet:

In the first step you need to retrieve the URL from the sheet and to extract the Id - you can do the latter e.g. with string.split()
In the next step you can addrees and rename the file with the DriveApp method DriveApp.getFileById(id).setName(name) +Thereby the name can be either prechosen by you or input by the form submitter in the designated form field.

Based on the previous solution I provided you, here is a modification which renames the filed to the name input in the respective form field:

function myFunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet=ss.getActiveSheet();
  var lastRow=ss.getLastRow();
  //the first column contains the timestamp
  var department=sheet.getRange(lastRow,2).getValue();
  var subjectCode=sheet.getRange(lastRow,3).getValue();
  var courseCode=sheet.getRange(lastRow,4).getValue();
  var courseNo=sheet.getRange(lastRow,5).getValue();
  var newResponse=courseCode.toString()+courseNo.toString()+"-"+subjectCode.toString();
  sheet.getRange(lastRow,8).setValue(newResponse);
  var secodarySheetId;
  switch(department) {
  case "Physics":
    secodarySheetId="XXX";//Paste here the Id of the destinationsheet in the Physics folder
    break;
  case "Chemistry":
    secodarySheetId="XXX";//Paste here the Id of the destinationsheet in the Chemistry folder
    break;
  case "Math":
    secodarySheetId="XXX";//Paste here the Id of the destinationsheet in the Maths folder
    break;
  }

 var rowContents=sheet.getRange(lastRow,1,1,sheet.getLastColumn()).getValues();
 SpreadsheetApp.openById(secodarySheetId).getSheetByName("Sheet1").appendRow(rowContents[0]);
  //Here is the part retrieving the file, the desired new name and renaming the file
  var url=sheet.getRange(lastRow,6).getValue();
  var regex_ids = /\/file\/d\/([^\/]+)/;
  var Id = url.split('=');//.match(/[-\w]{25,}/); //regex_ids.exec(url);
  var newName=sheet.getRange(lastRow,7).getValue();
  DriveApp.getFileById(Id).setName(newName)
}

score 1 · Answer 2 · answered Oct 10 '19 at 12:39

Dears, I have completed the script with the help of stackoverflow's people comments and helpful codes with some modifications. I paste the code below . Thank you

   function myFunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();

  var sheet=ss.getActiveSheet();
  var lastRow=ss.getLastRow();
  //the first column contains the timestamp
  var Department=sheet.getRange(lastRow,2).getValue();
  var CourseSubject=sheet.getRange(lastRow,3).getValue();
  var CourseCode=sheet.getRange(lastRow,4).getValue();
  var CourseNo=sheet.getRange(lastRow,5).getValue();
  var ClassCode=sheet.getRange(lastRow,6).getValue();
  var Staff=sheet.getRange(lastRow,7).getValue();
  var url=sheet.getRange(lastRow,8).getValue();

  var newResponse=CourseCode.toString()+CourseNo.toString()+"-"+ClassCode.toString();
  sheet.getRange(lastRow,9).setValue(newResponse);

  var secodarySheetId;
  switch(Department) {
  case "Chemistry":
    secodarySheetId="1YrkaAil_tJK1WKnhzAiDs6vntqAWFvxL";//Paste here the Id of the destinationsheet in the Physics folder
    break;
  case "Physics":
    secodarySheetId="168b6JIltI03EmOgV55984WfxJ5vhe49m";//Paste here the Id of the destinationsheet in the Physics folder
    break;
  case "Math":
    secodarySheetId="1Zq32ttSz_LrXVTiQkXGIegBEc0v1f5uY";//Paste here the Id of the destinationsheet in the Physics folder
    break;
   case "Computer Science":
    secodarySheetId="1QeWtEBtw1iI9IKZeC5-aXgifNV-2GgVD";//Paste here the Id of the destinationsheet in the Physics folder
    break;
   case "Biology":
    secodarySheetId="1bkwjWR-WkHGc3j3LxuR_pJ7wLP8fb0SV";//Paste here the Id of the destinationsheet in the Physics folder
    break;
  }

  var rowContents=sheet.getRange(lastRow,1,1,sheet.getLastColumn()).getValues();  


//  var dataFolder="1fQKYII5FpRQRbaYdoUUa3b2rjesr2K_4nHDQJzg22uk"


  //create a new folder with a specific nane, say newResponse
//  var parentFolder=DriveApp.getFolderById(secodarySheetId);
//  var newFolder=parentFolder.createFolder(newResponse)
// Logger.log(getFileName('13NeENGwL9FWtMYnLV9r5P8kbqxjlNo8d'));

  Logger.log(getFileName(secodarySheetId));
  // Department folders 
  var DepartmentFolder = getFileName(secodarySheetId);

  // Change file name
  var MainFolder = DriveApp.getFolderById("0ByV7HytJeZ-5fkota3lRQ3llbGczb2lnMmV4dXN6cTZWa2VSZVljOWkzVDlGb2w0MHlBdUE"); // change accordingly to  folder ID
  var files = MainFolder.getFiles(); 
// new name of csv that I want to rename the file within my google drive folder
    while(files.hasNext())
    {        // iterate throught the csv files available
        var file = files.next()
        // will rename all csv's to processed
        file.setName(newResponse);
    }



  DriveApp.getFolderById(secodarySheetId).addFile(file); 
  MainFolder.removeFile(file);


  }

AxelSariel · Answer 3 · 2020-06-13T00:05:08.073

I had the same issue today and came across this question.

I adapted it to iterate through all the rows automatically, along with some variables to make it easier to customize (choosing Name Column and URL Column).

It has functions for either renaming "all" the records (useful when you are renaming an existing form's records what I did :) or just the "last" one. The last one is nice to pair with an OnFormSubmitted Trigger (Targeting the "last" function) to avoid going through all the records every time, you can read more about it here.

Thank you for sharing your code, it really helped me out!

Here's the code if anyone needs it:

var sheet=ss.getActiveSheet();
var rangeData = sheet.getDataRange();
var lastColumn = rangeData.getLastColumn();
var lastRow = rangeData.getLastRow();
// Notice the second 2, this is to avoid the Timestamp Column
var searchRange = sheet.getRange(2,2, lastRow-1, lastColumn-1);

// Replace with your values (Column A=1, B=2, etc...)
var nameColumn = 2; // B
var urlColumn = 5; // E

// Calculating index for array
nameColumn -= 2;
urlColumn -= 2;

// Use this to rename the last record
function last() {
  var lastRowContents=sheet.getRange(lastRow,2,1,sheet.getLastColumn()).getValues()[0];
  rename(lastRowContents);
}

// Use this to rename all records
function all() {
  // Put rows in an array
  var rangeValues = searchRange.getValues();
  // Loop through the rows and rename file
    for ( i = 0 ; i < lastRow - 1; i++){
      row = rangeValues[i];
      rename(row);
    };
}

// Retrieves the ID and Name fields from the row, then
// renames the file
function rename(row) {

  // Using the first field, Name (Index 0 becuse of the array, calculated above)
  // ** Even though the Name field is the second column, we see it as the first one since 
  //    we ignored the timestamp column in the searchRange **
  var userName = row[nameColumn];
  var url = row[urlColumn];

  // Retrieve the ID from the URL
  var Id = url.split('=')[1];

  // Adapt this newFileName to your needs
  var newFileName = userName;

  // Get the file and rename it
  DriveApp.getFileById(Id).setName(newFileName);
  Logger.log("Renamed file with ID " + Id + " to " + newFileName);

};

Can I use a form field to rename upload pdf or doc using googlescript?

3 Answers3