1

I'm getting xlsx files from an ERP into a google drive folder (by placing them into the "Drive for PC" application folder). I want them to feed a master Google Sheet that makes some drawings and dynamic tables with the information from those files. In this master sheet I use the function importrange(key sheet), so I need to convert the source xlsx into gsheets files.

I found this question which helped me solve the first problem, "how to auto convert files in the drive folder automatically":

function convert() {
  var carpeta = DriveApp.getFolderById('idfolder');
  aconvertir = carpeta.getFiles();  
  while (aconvertir.hasNext()) {
    var archivo = aconvertir.next();
    var name = archivo.getName();
    var ID = archivo.getId();
    var xBlob = archivo.getBlob();
    var newFile = { title : name,
      key : ID,
      "parents": [{"id": "my id parent folder "} ] 
    }
    file = Drive.Files.insert(newFile, xBlob, {convert: true});
  }
}

The problem here is that I need to update the content of the converted Google Sheet as the content of the xlsx is not static (it gets changed daily). So every day I'd like to run a script which updates the previously generated gsheets content. I wrote this script to try to update them:

function pbafor() {
  xlsx = DriveApp.getFolderById('folder_with_xlsx').getFiles();
  gSheet = DriveApp.getFolderById('folder_with_gsheets_to_update').getFiles();
  while (xlsx.hasNext()) {
    var xBlob = xlsx.next().getBlob(); 
    var archDestino = {
      title: xlsx.next().getName(),
    }
    var idReemplazo = gSheet.next().getId();
    nvo = Drive.Files.update(archDestino, idReemplazo, xBlob);
  }
}

The problem with this script is that it only updates the first element of the folder_with_gsheets_to_update folder.

(note: if I try to get the blob, var xBlob=xlsx.next().getBlob(), if the file is a gsheet the apps script will show a server error. This server error is absent if the file is an xslx mimetype.)

tehhowch
  • 9,645
  • 4
  • 24
  • 42
cristian
  • 11
  • 4
  • You cannot update the contents of non-Google Spreadsheet spreadsheets from within apps Script. You will need to convert them to a Google Spreadsheet format and manipulate that sheet with apps script. Assuming I get what you're asking. – Douglas Gaskell Apr 25 '16 at 05:03
  • hi , thanks for answering, with the script above i converte the xlsx files into gsheets drive, then I try to use this script to update the content of the generated folders. ( i refresh the original answer with this script) If i try to get the blob data from a google sheet the apps script says that there's an error on server. But if i get the blob of the xlsx, the script works fine. The problem is that the script only update the first element on the target folder. – cristian Apr 25 '16 at 13:44
  • 1
    One issue with your `pbafor` loop is that you double-advance through the `xlsx` collection, by calling `xlsx.next()` twice within a single iteration of the while-loop. Another is that you assume the collections are equivalently ordered, when you should be checking through the gsheets collection until you find the file that matches the current xslx file (maybe you could store the relationship between xlsx id and created gsheet id in a spreadsheet and compare to it?). It also looks like you have some globals, e.g. `nvo`? If so, they are not preserved between **any** script executions. – tehhowch Mar 13 '18 at 16:41

0 Answers0