CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Nhong
Nhong

Reputation: 109

Script for looping through multiple spreadsheets doesn't work

I have made a script to loop through multiple spreadsheets for 2 purposes.

The problem is my script ran but didn't do anything.

Because I'm just a starter who learn to make a script.(My basic of coding is zero.) I don't know why my script didn't do anything.

Any some advice would be highly appreciated.

function myFunction() {
var idSheet = SpreadsheetApp.openById('myspreadsheetID').getSheetByName('คีย์บัญชี');
var todayDate = new Date();
todayDate.setHours(0,0,0,0);
var last7DaysDate = new Date();
last7DaysDate.setHours(0,0,0,0);
last7DaysDate.setDate(todayDate.getDate()-7);

for(var x=4;x<=6;x++){
var id = idSheet.getRange(x, 2).getValue();

var s = SpreadsheetApp.openById(id);
var ss = s.getSheetByName('คีย์บัญชี');

//set format วันที่ ตัวอย่างเช่น พฤ. 26
var rangeToFormat = ss.getRange('C2:C');
rangeToFormat.setNumberFormat('ddd D');

var data = ss.getRange('C1:C').getValues();  

for(var i = 0; i<data.length; i++){
  var fDate = new Date(data[i]);
  if (fDate.getDate() == last7DaysDate.getDate() && 
      fDate.getMonth() == last7DaysDate.getMonth() && 
      fDate.getFullYear() == last7DaysDate.getFullYear()) {

        var protection = ss.protect();  
        var lastRow = ss.getLastRow();
        var unprotected1 = ss.getRange(i+1,3,lastRow-i,7); //คีย์รายการขาย
        var unprotected2 = ss.getRange('X10:AB25'); //ประวัติการตัดยอดฝาก
        var unprotected3 = ss.getRange('X31:AB41'); //ค่าใช้จ่ายประจำเดือน
        protection.setUnprotectedRanges([unprotected1,unprotected2,unprotected3]);
      }
}

}

}

Upvotes: 2

Views: 112

Answers (1)

Diego
Diego

Reputation: 9571

สวัสดีครับคุณน้อง

This does work.

First, check to make sure that you have permissions to edit the spreadsheets.

Check that you're using the correct id. Try inserting Logger.log() statements to see what values the script is working with. 

for(var x=4;x<=6;x++){
  var id = idSheet.getRange(x, 2).getValue();
  Logger.log("x: " x + "ID: " + id);
  ...
}

Run the script, then view the Logs

enter image description here

Next, check your condition values. Use Logger.log() again. Specifically, check last7DaysDate:

Logger.log("Last7Date: " + last7DaysDate.getDate() + "/" + (last7DaysDate.getMonth()+1) + "/" + last7DaysDate.getFullYear())

Note that the value of .getMonth() is zero-indexed, so you should add 1 to display the expected value.

Then, look at the date values that you're comparing against. 

Logger.log("fDate: "+ fDate.getDate() + "/" + (fDate.getMonth()+1) + "/" + fDate.getFullYear());

I ran your script after making changes only to the protected ranges X10:AB25 and X31:AB41. It ran without errors, changed the formatting, and set the sheet protections.

enter image description here

โชคดีนะครับ

Upvotes: 1

Related Questions