Google Apps Script creates sheets version of excel file. Issue with multiple creation of versions.












0















I found a solution for my original question in another post Google Apps Script creates sheets version of excel file.



Testing with the code provided in the answer I ran into another issue. Every time I run the script it creates the Spreadsheets version of the .xlsx files again even if they already exist. I have tried modifying the code withing the last If with no results. Then went back to run your code as posted in case I have missed something but it keeps creating versions of the same files.



Any idea of what could I do to fix this will be really appreciated.



The code provided int he answer is the following.






// Convert the user's stored excel files to google spreadsheets based on the specified directories.
// There are quota limits on the maximum conversions per day: consumer @gmail = 250.
function convertExcelToGoogleSheets()
{
var user = Session.getActiveUser(); // Used for ownership testing.
var origin = DriveApp.getFolderById("origin folder id");
var dest = DriveApp.getFolderById("destination folder id");

// Index the filenames of owned Google Sheets files as object keys (which are hashed).
// This avoids needing to search and do multiple string comparisons.
// It takes around 100-200 ms per iteration to advance the iterator, check if the file
// should be cached, and insert the key-value pair. Depending on the magnitude of
// the task, this may need to be done separately, and loaded from a storage device instead.
// Note that there are quota limits on queries per second - 1000 per 100 sec:
// If the sequence is too large and the loop too fast, Utilities.sleep() usage will be needed.
var gsi = dest.getFilesByType(MimeType.GOOGLE_SHEETS), gsNames = {};
while (gsi.hasNext())
{
var file = gsi.next();
if(file.getOwner().getEmail() == user.getEmail())
gsNames[file.getName()] = true;
}

// Find and convert any unconverted .xls, .xlsx files in the given directories.
var exceltypes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];
for(var mt = 0; mt < exceltypes.length; ++mt)
{
var efi = origin.getFilesByType(exceltypes[mt]);
while (efi.hasNext())
{
var file = efi.next();
// Perform conversions only for owned files that don't have owned gs equivalents.
// If an excel file does not have gs file with the same name, gsNames[ ... ] will be undefined, and !undefined -> true
// If an excel file does have a gs file with the same name, gsNames[ ... ] will be true, and !true -> false
if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])
{
Drive.Files.insert(
{title: file.getName(), parents: [{"id": dest.getId()}]},
file.getBlob(),
{convert: true}
);
// Do not convert any more spreadsheets with this same name.
gsNames[file.getName()] = true;
}
}
}
}












share|improve this question























  • In order to understand correctly about your situation, can I ask you about what you want to do? You want to convert Excel files in origin folder to Google Spreadsheet and put the converted Spreadsheet to dest folder. At this time, when the filename of converted file is existing in dest folder, you don't want to convert it. Is my understanding correct?

    – Tanaike
    Nov 22 '18 at 1:00











  • Yes it is, the Excel files are uploaded to the Origin folder and the script creates a Google Spreadsheet version on the Destination folder. The script would be run multiple times in a day since more files are being uploaded to the original folder continuously. The script is intended to convert the Excel files which Don't have a spreadsheet version in the Dest folder yet, but it keeps creating versions for all of the files even if they already have an spreadsheet version created by script before.

    – Axel
    Nov 22 '18 at 1:09













  • Thank you for replying. I posted an answer for modifying script. Could you please confirm it? If that was not what you want, please tell me. I would like to modify it.

    – Tanaike
    Nov 22 '18 at 1:14
















0















I found a solution for my original question in another post Google Apps Script creates sheets version of excel file.



Testing with the code provided in the answer I ran into another issue. Every time I run the script it creates the Spreadsheets version of the .xlsx files again even if they already exist. I have tried modifying the code withing the last If with no results. Then went back to run your code as posted in case I have missed something but it keeps creating versions of the same files.



Any idea of what could I do to fix this will be really appreciated.



The code provided int he answer is the following.






// Convert the user's stored excel files to google spreadsheets based on the specified directories.
// There are quota limits on the maximum conversions per day: consumer @gmail = 250.
function convertExcelToGoogleSheets()
{
var user = Session.getActiveUser(); // Used for ownership testing.
var origin = DriveApp.getFolderById("origin folder id");
var dest = DriveApp.getFolderById("destination folder id");

// Index the filenames of owned Google Sheets files as object keys (which are hashed).
// This avoids needing to search and do multiple string comparisons.
// It takes around 100-200 ms per iteration to advance the iterator, check if the file
// should be cached, and insert the key-value pair. Depending on the magnitude of
// the task, this may need to be done separately, and loaded from a storage device instead.
// Note that there are quota limits on queries per second - 1000 per 100 sec:
// If the sequence is too large and the loop too fast, Utilities.sleep() usage will be needed.
var gsi = dest.getFilesByType(MimeType.GOOGLE_SHEETS), gsNames = {};
while (gsi.hasNext())
{
var file = gsi.next();
if(file.getOwner().getEmail() == user.getEmail())
gsNames[file.getName()] = true;
}

// Find and convert any unconverted .xls, .xlsx files in the given directories.
var exceltypes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];
for(var mt = 0; mt < exceltypes.length; ++mt)
{
var efi = origin.getFilesByType(exceltypes[mt]);
while (efi.hasNext())
{
var file = efi.next();
// Perform conversions only for owned files that don't have owned gs equivalents.
// If an excel file does not have gs file with the same name, gsNames[ ... ] will be undefined, and !undefined -> true
// If an excel file does have a gs file with the same name, gsNames[ ... ] will be true, and !true -> false
if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])
{
Drive.Files.insert(
{title: file.getName(), parents: [{"id": dest.getId()}]},
file.getBlob(),
{convert: true}
);
// Do not convert any more spreadsheets with this same name.
gsNames[file.getName()] = true;
}
}
}
}












share|improve this question























  • In order to understand correctly about your situation, can I ask you about what you want to do? You want to convert Excel files in origin folder to Google Spreadsheet and put the converted Spreadsheet to dest folder. At this time, when the filename of converted file is existing in dest folder, you don't want to convert it. Is my understanding correct?

    – Tanaike
    Nov 22 '18 at 1:00











  • Yes it is, the Excel files are uploaded to the Origin folder and the script creates a Google Spreadsheet version on the Destination folder. The script would be run multiple times in a day since more files are being uploaded to the original folder continuously. The script is intended to convert the Excel files which Don't have a spreadsheet version in the Dest folder yet, but it keeps creating versions for all of the files even if they already have an spreadsheet version created by script before.

    – Axel
    Nov 22 '18 at 1:09













  • Thank you for replying. I posted an answer for modifying script. Could you please confirm it? If that was not what you want, please tell me. I would like to modify it.

    – Tanaike
    Nov 22 '18 at 1:14














0












0








0








I found a solution for my original question in another post Google Apps Script creates sheets version of excel file.



Testing with the code provided in the answer I ran into another issue. Every time I run the script it creates the Spreadsheets version of the .xlsx files again even if they already exist. I have tried modifying the code withing the last If with no results. Then went back to run your code as posted in case I have missed something but it keeps creating versions of the same files.



Any idea of what could I do to fix this will be really appreciated.



The code provided int he answer is the following.






// Convert the user's stored excel files to google spreadsheets based on the specified directories.
// There are quota limits on the maximum conversions per day: consumer @gmail = 250.
function convertExcelToGoogleSheets()
{
var user = Session.getActiveUser(); // Used for ownership testing.
var origin = DriveApp.getFolderById("origin folder id");
var dest = DriveApp.getFolderById("destination folder id");

// Index the filenames of owned Google Sheets files as object keys (which are hashed).
// This avoids needing to search and do multiple string comparisons.
// It takes around 100-200 ms per iteration to advance the iterator, check if the file
// should be cached, and insert the key-value pair. Depending on the magnitude of
// the task, this may need to be done separately, and loaded from a storage device instead.
// Note that there are quota limits on queries per second - 1000 per 100 sec:
// If the sequence is too large and the loop too fast, Utilities.sleep() usage will be needed.
var gsi = dest.getFilesByType(MimeType.GOOGLE_SHEETS), gsNames = {};
while (gsi.hasNext())
{
var file = gsi.next();
if(file.getOwner().getEmail() == user.getEmail())
gsNames[file.getName()] = true;
}

// Find and convert any unconverted .xls, .xlsx files in the given directories.
var exceltypes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];
for(var mt = 0; mt < exceltypes.length; ++mt)
{
var efi = origin.getFilesByType(exceltypes[mt]);
while (efi.hasNext())
{
var file = efi.next();
// Perform conversions only for owned files that don't have owned gs equivalents.
// If an excel file does not have gs file with the same name, gsNames[ ... ] will be undefined, and !undefined -> true
// If an excel file does have a gs file with the same name, gsNames[ ... ] will be true, and !true -> false
if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])
{
Drive.Files.insert(
{title: file.getName(), parents: [{"id": dest.getId()}]},
file.getBlob(),
{convert: true}
);
// Do not convert any more spreadsheets with this same name.
gsNames[file.getName()] = true;
}
}
}
}












share|improve this question














I found a solution for my original question in another post Google Apps Script creates sheets version of excel file.



Testing with the code provided in the answer I ran into another issue. Every time I run the script it creates the Spreadsheets version of the .xlsx files again even if they already exist. I have tried modifying the code withing the last If with no results. Then went back to run your code as posted in case I have missed something but it keeps creating versions of the same files.



Any idea of what could I do to fix this will be really appreciated.



The code provided int he answer is the following.






// Convert the user's stored excel files to google spreadsheets based on the specified directories.
// There are quota limits on the maximum conversions per day: consumer @gmail = 250.
function convertExcelToGoogleSheets()
{
var user = Session.getActiveUser(); // Used for ownership testing.
var origin = DriveApp.getFolderById("origin folder id");
var dest = DriveApp.getFolderById("destination folder id");

// Index the filenames of owned Google Sheets files as object keys (which are hashed).
// This avoids needing to search and do multiple string comparisons.
// It takes around 100-200 ms per iteration to advance the iterator, check if the file
// should be cached, and insert the key-value pair. Depending on the magnitude of
// the task, this may need to be done separately, and loaded from a storage device instead.
// Note that there are quota limits on queries per second - 1000 per 100 sec:
// If the sequence is too large and the loop too fast, Utilities.sleep() usage will be needed.
var gsi = dest.getFilesByType(MimeType.GOOGLE_SHEETS), gsNames = {};
while (gsi.hasNext())
{
var file = gsi.next();
if(file.getOwner().getEmail() == user.getEmail())
gsNames[file.getName()] = true;
}

// Find and convert any unconverted .xls, .xlsx files in the given directories.
var exceltypes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];
for(var mt = 0; mt < exceltypes.length; ++mt)
{
var efi = origin.getFilesByType(exceltypes[mt]);
while (efi.hasNext())
{
var file = efi.next();
// Perform conversions only for owned files that don't have owned gs equivalents.
// If an excel file does not have gs file with the same name, gsNames[ ... ] will be undefined, and !undefined -> true
// If an excel file does have a gs file with the same name, gsNames[ ... ] will be true, and !true -> false
if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])
{
Drive.Files.insert(
{title: file.getName(), parents: [{"id": dest.getId()}]},
file.getBlob(),
{convert: true}
);
// Do not convert any more spreadsheets with this same name.
gsNames[file.getName()] = true;
}
}
}
}








// Convert the user's stored excel files to google spreadsheets based on the specified directories.
// There are quota limits on the maximum conversions per day: consumer @gmail = 250.
function convertExcelToGoogleSheets()
{
var user = Session.getActiveUser(); // Used for ownership testing.
var origin = DriveApp.getFolderById("origin folder id");
var dest = DriveApp.getFolderById("destination folder id");

// Index the filenames of owned Google Sheets files as object keys (which are hashed).
// This avoids needing to search and do multiple string comparisons.
// It takes around 100-200 ms per iteration to advance the iterator, check if the file
// should be cached, and insert the key-value pair. Depending on the magnitude of
// the task, this may need to be done separately, and loaded from a storage device instead.
// Note that there are quota limits on queries per second - 1000 per 100 sec:
// If the sequence is too large and the loop too fast, Utilities.sleep() usage will be needed.
var gsi = dest.getFilesByType(MimeType.GOOGLE_SHEETS), gsNames = {};
while (gsi.hasNext())
{
var file = gsi.next();
if(file.getOwner().getEmail() == user.getEmail())
gsNames[file.getName()] = true;
}

// Find and convert any unconverted .xls, .xlsx files in the given directories.
var exceltypes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];
for(var mt = 0; mt < exceltypes.length; ++mt)
{
var efi = origin.getFilesByType(exceltypes[mt]);
while (efi.hasNext())
{
var file = efi.next();
// Perform conversions only for owned files that don't have owned gs equivalents.
// If an excel file does not have gs file with the same name, gsNames[ ... ] will be undefined, and !undefined -> true
// If an excel file does have a gs file with the same name, gsNames[ ... ] will be true, and !true -> false
if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])
{
Drive.Files.insert(
{title: file.getName(), parents: [{"id": dest.getId()}]},
file.getBlob(),
{convert: true}
);
// Do not convert any more spreadsheets with this same name.
gsNames[file.getName()] = true;
}
}
}
}





// Convert the user's stored excel files to google spreadsheets based on the specified directories.
// There are quota limits on the maximum conversions per day: consumer @gmail = 250.
function convertExcelToGoogleSheets()
{
var user = Session.getActiveUser(); // Used for ownership testing.
var origin = DriveApp.getFolderById("origin folder id");
var dest = DriveApp.getFolderById("destination folder id");

// Index the filenames of owned Google Sheets files as object keys (which are hashed).
// This avoids needing to search and do multiple string comparisons.
// It takes around 100-200 ms per iteration to advance the iterator, check if the file
// should be cached, and insert the key-value pair. Depending on the magnitude of
// the task, this may need to be done separately, and loaded from a storage device instead.
// Note that there are quota limits on queries per second - 1000 per 100 sec:
// If the sequence is too large and the loop too fast, Utilities.sleep() usage will be needed.
var gsi = dest.getFilesByType(MimeType.GOOGLE_SHEETS), gsNames = {};
while (gsi.hasNext())
{
var file = gsi.next();
if(file.getOwner().getEmail() == user.getEmail())
gsNames[file.getName()] = true;
}

// Find and convert any unconverted .xls, .xlsx files in the given directories.
var exceltypes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];
for(var mt = 0; mt < exceltypes.length; ++mt)
{
var efi = origin.getFilesByType(exceltypes[mt]);
while (efi.hasNext())
{
var file = efi.next();
// Perform conversions only for owned files that don't have owned gs equivalents.
// If an excel file does not have gs file with the same name, gsNames[ ... ] will be undefined, and !undefined -> true
// If an excel file does have a gs file with the same name, gsNames[ ... ] will be true, and !true -> false
if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])
{
Drive.Files.insert(
{title: file.getName(), parents: [{"id": dest.getId()}]},
file.getBlob(),
{convert: true}
);
// Do not convert any more spreadsheets with this same name.
gsNames[file.getName()] = true;
}
}
}
}






excel google-apps-script google-sheets google-drive-sdk






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 21 '18 at 23:41









Axel Axel

102




102













  • In order to understand correctly about your situation, can I ask you about what you want to do? You want to convert Excel files in origin folder to Google Spreadsheet and put the converted Spreadsheet to dest folder. At this time, when the filename of converted file is existing in dest folder, you don't want to convert it. Is my understanding correct?

    – Tanaike
    Nov 22 '18 at 1:00











  • Yes it is, the Excel files are uploaded to the Origin folder and the script creates a Google Spreadsheet version on the Destination folder. The script would be run multiple times in a day since more files are being uploaded to the original folder continuously. The script is intended to convert the Excel files which Don't have a spreadsheet version in the Dest folder yet, but it keeps creating versions for all of the files even if they already have an spreadsheet version created by script before.

    – Axel
    Nov 22 '18 at 1:09













  • Thank you for replying. I posted an answer for modifying script. Could you please confirm it? If that was not what you want, please tell me. I would like to modify it.

    – Tanaike
    Nov 22 '18 at 1:14



















  • In order to understand correctly about your situation, can I ask you about what you want to do? You want to convert Excel files in origin folder to Google Spreadsheet and put the converted Spreadsheet to dest folder. At this time, when the filename of converted file is existing in dest folder, you don't want to convert it. Is my understanding correct?

    – Tanaike
    Nov 22 '18 at 1:00











  • Yes it is, the Excel files are uploaded to the Origin folder and the script creates a Google Spreadsheet version on the Destination folder. The script would be run multiple times in a day since more files are being uploaded to the original folder continuously. The script is intended to convert the Excel files which Don't have a spreadsheet version in the Dest folder yet, but it keeps creating versions for all of the files even if they already have an spreadsheet version created by script before.

    – Axel
    Nov 22 '18 at 1:09













  • Thank you for replying. I posted an answer for modifying script. Could you please confirm it? If that was not what you want, please tell me. I would like to modify it.

    – Tanaike
    Nov 22 '18 at 1:14

















In order to understand correctly about your situation, can I ask you about what you want to do? You want to convert Excel files in origin folder to Google Spreadsheet and put the converted Spreadsheet to dest folder. At this time, when the filename of converted file is existing in dest folder, you don't want to convert it. Is my understanding correct?

– Tanaike
Nov 22 '18 at 1:00





In order to understand correctly about your situation, can I ask you about what you want to do? You want to convert Excel files in origin folder to Google Spreadsheet and put the converted Spreadsheet to dest folder. At this time, when the filename of converted file is existing in dest folder, you don't want to convert it. Is my understanding correct?

– Tanaike
Nov 22 '18 at 1:00













Yes it is, the Excel files are uploaded to the Origin folder and the script creates a Google Spreadsheet version on the Destination folder. The script would be run multiple times in a day since more files are being uploaded to the original folder continuously. The script is intended to convert the Excel files which Don't have a spreadsheet version in the Dest folder yet, but it keeps creating versions for all of the files even if they already have an spreadsheet version created by script before.

– Axel
Nov 22 '18 at 1:09







Yes it is, the Excel files are uploaded to the Origin folder and the script creates a Google Spreadsheet version on the Destination folder. The script would be run multiple times in a day since more files are being uploaded to the original folder continuously. The script is intended to convert the Excel files which Don't have a spreadsheet version in the Dest folder yet, but it keeps creating versions for all of the files even if they already have an spreadsheet version created by script before.

– Axel
Nov 22 '18 at 1:09















Thank you for replying. I posted an answer for modifying script. Could you please confirm it? If that was not what you want, please tell me. I would like to modify it.

– Tanaike
Nov 22 '18 at 1:14





Thank you for replying. I posted an answer for modifying script. Could you please confirm it? If that was not what you want, please tell me. I would like to modify it.

– Tanaike
Nov 22 '18 at 1:14












1 Answer
1






active

oldest

votes


















0















  • You want to convert Excel files in origin folder to Google Spreadsheet and put the converted Spreadsheet to dest folder.

  • When the filename of converted file is existing in dest folder, you don't want to convert it.


If my understanding is correct, how about this modification?



From:



if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])


To:



if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName().split(".")[0]])


Note:




  • In this modification, when the filename of converted file is found in the dest folder, the file is not converted.

  • When the filename has the extension like ###.xlsx and it is converted to Google Spreadsheet, it seems that the extension is automatically removed. I think that this is the reason that the duplicated files are created. So I used split(".")[0] for this situation.


Reference:




  • split()






share|improve this answer


























  • I Couldn't thank you enough, this worked just perfectly. I wouldn't be able to figured it out my delft. I guess I need to study more JavaScript and Apps Script itself. I'll study the code you added to try to understand it and check the reference to MDM you added. Thank you very much.

    – Axel
    Nov 22 '18 at 2:26











  • @Axel I'm really sorry. I noticed that I had misunderstood about the script. So I updated my answer. Could you please confirm it? By using the object gsNames in your script, the modification part can be more simple.

    – Tanaike
    Nov 22 '18 at 2:57











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421980%2fgoogle-apps-script-creates-sheets-version-of-excel-file-issue-with-multiple-cre%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0















  • You want to convert Excel files in origin folder to Google Spreadsheet and put the converted Spreadsheet to dest folder.

  • When the filename of converted file is existing in dest folder, you don't want to convert it.


If my understanding is correct, how about this modification?



From:



if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])


To:



if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName().split(".")[0]])


Note:




  • In this modification, when the filename of converted file is found in the dest folder, the file is not converted.

  • When the filename has the extension like ###.xlsx and it is converted to Google Spreadsheet, it seems that the extension is automatically removed. I think that this is the reason that the duplicated files are created. So I used split(".")[0] for this situation.


Reference:




  • split()






share|improve this answer


























  • I Couldn't thank you enough, this worked just perfectly. I wouldn't be able to figured it out my delft. I guess I need to study more JavaScript and Apps Script itself. I'll study the code you added to try to understand it and check the reference to MDM you added. Thank you very much.

    – Axel
    Nov 22 '18 at 2:26











  • @Axel I'm really sorry. I noticed that I had misunderstood about the script. So I updated my answer. Could you please confirm it? By using the object gsNames in your script, the modification part can be more simple.

    – Tanaike
    Nov 22 '18 at 2:57
















0















  • You want to convert Excel files in origin folder to Google Spreadsheet and put the converted Spreadsheet to dest folder.

  • When the filename of converted file is existing in dest folder, you don't want to convert it.


If my understanding is correct, how about this modification?



From:



if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])


To:



if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName().split(".")[0]])


Note:




  • In this modification, when the filename of converted file is found in the dest folder, the file is not converted.

  • When the filename has the extension like ###.xlsx and it is converted to Google Spreadsheet, it seems that the extension is automatically removed. I think that this is the reason that the duplicated files are created. So I used split(".")[0] for this situation.


Reference:




  • split()






share|improve this answer


























  • I Couldn't thank you enough, this worked just perfectly. I wouldn't be able to figured it out my delft. I guess I need to study more JavaScript and Apps Script itself. I'll study the code you added to try to understand it and check the reference to MDM you added. Thank you very much.

    – Axel
    Nov 22 '18 at 2:26











  • @Axel I'm really sorry. I noticed that I had misunderstood about the script. So I updated my answer. Could you please confirm it? By using the object gsNames in your script, the modification part can be more simple.

    – Tanaike
    Nov 22 '18 at 2:57














0












0








0








  • You want to convert Excel files in origin folder to Google Spreadsheet and put the converted Spreadsheet to dest folder.

  • When the filename of converted file is existing in dest folder, you don't want to convert it.


If my understanding is correct, how about this modification?



From:



if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])


To:



if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName().split(".")[0]])


Note:




  • In this modification, when the filename of converted file is found in the dest folder, the file is not converted.

  • When the filename has the extension like ###.xlsx and it is converted to Google Spreadsheet, it seems that the extension is automatically removed. I think that this is the reason that the duplicated files are created. So I used split(".")[0] for this situation.


Reference:




  • split()






share|improve this answer
















  • You want to convert Excel files in origin folder to Google Spreadsheet and put the converted Spreadsheet to dest folder.

  • When the filename of converted file is existing in dest folder, you don't want to convert it.


If my understanding is correct, how about this modification?



From:



if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])


To:



if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName().split(".")[0]])


Note:




  • In this modification, when the filename of converted file is found in the dest folder, the file is not converted.

  • When the filename has the extension like ###.xlsx and it is converted to Google Spreadsheet, it seems that the extension is automatically removed. I think that this is the reason that the duplicated files are created. So I used split(".")[0] for this situation.


Reference:




  • split()







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 22 '18 at 2:57

























answered Nov 22 '18 at 1:14









TanaikeTanaike

20.3k21022




20.3k21022













  • I Couldn't thank you enough, this worked just perfectly. I wouldn't be able to figured it out my delft. I guess I need to study more JavaScript and Apps Script itself. I'll study the code you added to try to understand it and check the reference to MDM you added. Thank you very much.

    – Axel
    Nov 22 '18 at 2:26











  • @Axel I'm really sorry. I noticed that I had misunderstood about the script. So I updated my answer. Could you please confirm it? By using the object gsNames in your script, the modification part can be more simple.

    – Tanaike
    Nov 22 '18 at 2:57



















  • I Couldn't thank you enough, this worked just perfectly. I wouldn't be able to figured it out my delft. I guess I need to study more JavaScript and Apps Script itself. I'll study the code you added to try to understand it and check the reference to MDM you added. Thank you very much.

    – Axel
    Nov 22 '18 at 2:26











  • @Axel I'm really sorry. I noticed that I had misunderstood about the script. So I updated my answer. Could you please confirm it? By using the object gsNames in your script, the modification part can be more simple.

    – Tanaike
    Nov 22 '18 at 2:57

















I Couldn't thank you enough, this worked just perfectly. I wouldn't be able to figured it out my delft. I guess I need to study more JavaScript and Apps Script itself. I'll study the code you added to try to understand it and check the reference to MDM you added. Thank you very much.

– Axel
Nov 22 '18 at 2:26





I Couldn't thank you enough, this worked just perfectly. I wouldn't be able to figured it out my delft. I guess I need to study more JavaScript and Apps Script itself. I'll study the code you added to try to understand it and check the reference to MDM you added. Thank you very much.

– Axel
Nov 22 '18 at 2:26













@Axel I'm really sorry. I noticed that I had misunderstood about the script. So I updated my answer. Could you please confirm it? By using the object gsNames in your script, the modification part can be more simple.

– Tanaike
Nov 22 '18 at 2:57





@Axel I'm really sorry. I noticed that I had misunderstood about the script. So I updated my answer. Could you please confirm it? By using the object gsNames in your script, the modification part can be more simple.

– Tanaike
Nov 22 '18 at 2:57


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421980%2fgoogle-apps-script-creates-sheets-version-of-excel-file-issue-with-multiple-cre%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

404 Error Contact Form 7 ajax form submitting

How to know if a Active Directory user can login interactively

TypeError: fit_transform() missing 1 required positional argument: 'X'